Notes

.
alter session set current_schema = APPS


audit form

audit form correction


Business Criticality

BUG-00070593
Guruprasad Veerappa
Ajay Mittapalli
11. WIP - Assigned


S1 - Critical


BUG-00070520


Nadine White


Damodara Rao Dharmapuri


12. WIP - Development


S3 - Medium



IF :xxvm_okc_ocef_audit_tbl.status not in ('MANUAL') and :xxvm_okc_ocef_audit_tbl.ERROR_CODE = 'CHANNEL_CHANGE' and :xxvm_okc_ocef_audit_tbl.NEW_CHANNEL IS NULL
THEN
SET_ITEM_PROPERTY ('NEW_CHANNEL', REQUIRED, PROPERTY_TRUE);
END IF;


IF :MASS_UPDATE.RSTATUS not in ('MANUAL') and
:MASS_UPDATE.ERRORCODE='CHANNEL_CHANGE' and :MASS_UPDATE.CHANNELTO IS NULL THEN
--GO_ITEM(:MASS_UPDATE.CHANNELTO);
SET_ITEM_PROPERTY ( 'CHANNELTO',REQUIRED ,PROPERTY_TRUE);
--vm_show_message('The record(S) having error code as CHANNEL CHANGE, CHANNEL-TO is Mandatory');
END IF;


IF :MASS_UPDATE.RSTATUS not in ('MANUAL') and


alter session set current_schema = APPS

JH0A3-6KKF0-Q806C-9R380-C4P70

NJ4CL-6K2A2-7886C-9C2K0-CXF54

--select sfdc_case_number,status,error_code,new_channel,last_update_date,sysdate from apps.xxvm_okc_ocef_audit_tbl where paclicense=:pac
--order by last_update_date desc

select paclicense,sfdc_case_number,status,error_code,new_channel,last_update_date,sysdate from apps.xxvm_okc_ocef_audit_hist_tbl where paclicense=:pac
order by last_update_date desc

SELECT COUNT(*) FROM apps.xxvm_okc_ocef_audit_hist_tbl where paclicense='JH0A3-6KKF0-Q806C-9R380-C4P70'
UNION
SELECT COUNT(*) FROM apps.xxvm_okc_ocef_audit_bl where paclicense='NJ4CL-6K2A2-7886C-9C2K0-CXF54'

SELECT COUNT(*) FROM apps.xxvm_okc_ocef_audit_tbl where paclicense=:pac

SELECT COUNT(*) FROM apps.xxvm_okc_ocef_audit_hist_tbl where paclicense=:PAC

J5094-6AK6N-K89ZC-89C20-1593M
Pidilite



alter session set current_schema = APPS
DEV11
apps
m1n5vdjux8
vmwdev11
cd $AU_TOP/forms/US

pidilite -
150- 6/

Audit FORM and Audit Form Correction Done. and Deployed to TEST11.

PPM -
DR -

Open Issues --

Found Bug , corrected in DEV11
Migrate the same on Monday = 6-Jan-2014



Transaction Accounts

Account No. / Nick name Branch Available Balance Transactions
00000033099492924 SRIKAKULAM Click here for balance Click here for last 10 transactions

Loan Accounts

Account No. / Nick name Branch Outstanding Amt Transactions
00000033105319082 SRIKAKULAM Click here for balance Click here for last 10 transactions
00000033105277992 SRIKAKULAM Click here for balance Click here for last 10 transactions

----------------------------------------------------------------
03-JAN-2014
----------------------------------------------------------------
9686602453

00063893
:SYSTEM.RECORD_STATUS = 'CHANGED'


1 - union ( vmware)
2- ERROR CODE -- success are we sending ?

if sending what is the error code ?

IF :XXVM_OKC_OCEF_AUDIT_TBL.STATUS='FAILED' AND :XXVM_OKC_OCEF_AUDIT_TBL.ERROR_CODE='CHANNEL_CHANGE' AND :XXVM_OKC_OCEF_AUDIT_TBL.NEW_CHANNEL IS NULL THEN
GO_ITEM(:XXVM_OKC_OCEF_AUDIT_TBL.NEW_CHANNEL);
SET_ITEM_PROPERTY ( 'NEW_CHANNEL',REQUIRED ,PROPERTY_TRUE);
VM_SHOW_MESSAGE('The record(s) having Error code as Chnnel Change should have value for CHANNEL-TO');
END IF;


IF :SYSTEM.RECORD_STATUS = 'CHANGED' THEN

IF :XXVM_OKC_OCEF_AUDIT_TBL.STATUS='FAILED' AND :XXVM_OKC_OCEF_AUDIT_TBL.ERROR_CODE='CHANNEL_CHANGE' AND :XXVM_OKC_OCEF_AUDIT_TBL.NEW_CHANNEL IS NULL THEN
SET_ITEM_PROPERTY ( 'NEW_CHANNEL',REQUIRED ,PROPERTY_TRUE);
END IF;



SELECT status,ERROR_CODE,error_message,NEW_CHANNEL,LAST_UPDATE_DATE,SYSDATE FROM apps.XXVM_OKC_OCEF_AUDIT_HIST_TBL WHERE PACLICENSE=:PAC
ORDER BY LAST_UPDATE_DATE DESC

500AJ-6KJC0-38162-9C380-38WN4
10423-2K1CK-Q816A-99CR4-34PQ4


03-JAN-2014

PARTNER1_LOV


PARTNER_LOV

SELECT VMware PARTNER_IDENTIFIER FROM DUAL

----------------------------------------------------------------------------------------------------------------------------------------------
02-JAN-2014
----------------------------------------------------------------------------------------------------------------------------------------------

https://vmshare.vmware.com/it/middleware/ofm/support/Shared%20Documents/Support/SOAEnvironments.htm


[email protected]

DECLARE
l_ERRCODE VARCHAR2(500);
l_intcode varchar2(1000);
l_var number(10);
BEGIN
IF :XXVM_OKC_OCEF_AUDIT_TBL.ERROR_CODE='CHANNEL_CHANGE' AND :XXVM_OKC_OCEF_AUDIT_TBL.NEW_CHANNEL IS NULL THEN
GO_ITEM(:XXVM_OKC_OCEF_AUDIT_TBL.NEW_CHANNEL);
SET_ITEM_PROPERTY ( 'NEW_CHANNEL',REQUIRED ,PROPERTY_TRUE);
VM_SHOW_MESSAGE('The record(s) having Error code as Chnnel Change should have value for CHANNEL-TO');
END IF;

SET_ITEM_PROPERTY ( 'NEW_CHANNEL',REQUIRED ,PROPERTY_FALSE);
END;


----------------------------------------------------------------------------------------------------------------------------------------------
01-JAN-2014
----------------------------------------------------------------------------------------------------------------------------------------------

when validate error_code


IF :XXVM_OKC_OCEF_AUDIT_TBL.ERROR_CODE='CHANNEL_CHANGE' THEN
GO_ITEM(:XXVM_OKC_OCEF_AUDIT_TBL.NEW_CHANNEL);
SET_ITEM_PROPERTY ( 'NEW_CHANNEL',REQUIRED ,PROPERTY_TRUE);
VM_SHOW_MESSAGE('The record(S) having error code as CHANNEL CHANGE, CHANNEL-TO is Mandatory');
END IF;
SET_ITEM_PROPERTY ( 'NEW_CHANNEL',REQUIRED ,PROPERTY_FALSE);

-----------------------------------------------------------------------------------------------------------------------------------------
KEY-COMMIT
KEY-EXIT

IF :XXVM_OKC_OCEF_AUDIT_TBL.ERROR_CODE='CHANNEL_CHANGE' AND :XXVM_OKC_OCEF_AUDIT_TBL.NEW_CHANNEL IS NULL THEN
VM_SHOW_MESSAGE('The record(S) having error code as CHANNEL CHANGE, CHANNEL-TO is Mandatory');
SET_ITEM_PROPERTY ( 'NEW_CHANNEL',REQUIRED ,PROPERTY_TRUE);
GO_ITEM(:XXVM_OKC_OCEF_AUDIT_TBL.NEW_CHANNEL);

RAISE FORM_TRIGGER_FAILURE;
END IF;

MASSUPDATE

ERRORCODE - WHENVALIDATE

IF :MASS_UPDATE.ERRORCODE='CHANNEL_CHANGE' THEN
GO_ITEM(:MASS_UPDATE.CHANNELTO);
SET_ITEM_PROPERTY ( 'CHANNELTO',REQUIRED ,PROPERTY_TRUE);
vm_show_message('The record(S) having error code as CHANNEL CHANGE, CHANNEL-TO is Mandatory');
END IF;


----------------------------------------------------------------------------------------------------------------------------------------------

CORRECTION
-------------

COMMENTED BELOW
-- IF :xxvm_okc_ocef_audit_tbl.STATUS = 'SUCCESS' THEN
-- :xxvm_okc_ocef_audit_tbl.ERROR_CODE:=NULL;
-- :xxvm_okc_ocef_audit_tbl.ERROR_MESSAGE:=NULL;
-- END IF;


KEY-COMMIT
KEY-EXIT

IF :XXVM_OKC_OCEF_AUDIT_TBL.ERROR_CODE='CHANNEL_CHANGE' AND :XXVM_OKC_OCEF_AUDIT_TBL.NEW_CHANNEL IS NULL THEN
VM_SHOW_MESSAGE('The record(S) having error code as CHANNEL CHANGE, CHANNEL-TO is Mandatory');
SET_ITEM_PROPERTY ( 'NEW_CHANNEL',REQUIRED ,PROPERTY_TRUE);
GO_ITEM(:XXVM_OKC_OCEF_AUDIT_TBL.NEW_CHANNEL);

RAISE FORM_TRIGGER_FAILURE;
END IF;

when validate error_code
--------------------------

IF :XXVM_OKC_OCEF_AUDIT_TBL.ERROR_CODE='CHANNEL_CHANGE' THEN
GO_ITEM(:XXVM_OKC_OCEF_AUDIT_TBL.NEW_CHANNEL);
SET_ITEM_PROPERTY ( 'NEW_CHANNEL',REQUIRED ,PROPERTY_TRUE);
VM_SHOW_MESSAGE('The record(S) having error code as CHANNEL CHANGE, CHANNEL-TO is Mandatory');
END IF;
SET_ITEM_PROPERTY ( 'NEW_CHANNEL',REQUIRED ,PROPERTY_FALSE);



----------------------------------------------------------------------------------------------------------------------------------------------




Hi Kumar

Here are the two things that need to be fixed (applies to both Audit and Audit Correction):

Channel To should be mandatory when error code is Channel Change

Channel To field should have VMware as an entry

:XXVM_OKC_OCEF_AUDIT_TBL.NEW_CHANNEL :=

I also saw two issues in the Transaction Deletion form:

I see there are multiple entries of the same record in the audit form. I did an export of the form and attached to this email.

When I delete one PAC and that PAC has 5 records in the form, then all 5 records are getting deleted.


I will call you to discuss the Trx Deletion form.

Thanks
Shakir Hussain,
[email protected]
650.427.7665 Office


----------------------------------------------------------------------------------------------------------------------------------------------
--11-DEC-2013
----------------------------------------------------------------------------------------------------------------------------------------------
alter session set current_schema = APPS


select oh.order_number "Order#",oh.header_id "Header ID"
,'Header is '||oh.flow_status_code "Header Status"
,oh.orig_sys_document_ref "Orig Sys",oh.creation_date "Order Created"
,ol.line_id "Line ID",ol.ordered_item "Ordered Item"
,'Line is '||ol.flow_status_code "Line Status",ol.ordered_quantity "Qty"
,msib.comms_nl_trackable_flag "IB",cii.instance_id "Instance Id"
,cii.creation_date "IB Created"
,msib.inventory_item_id "Item ID"--,ol.*,oh.*
,cis.name "IB Status",fu.description "Created by"
from oe_order_lines_all ol
,oe_order_headers_all oh
,mtl_system_items_b msib
,csi_item_instances cii
,csi_instance_statuses cis
,fnd_user fu
where ol.header_id = oh.header_id
and oh.order_number = :p_order_number
--and oh.header_id = :p_header_id
and msib.inventory_item_id = ol.inventory_item_id
and cii.last_oe_order_line_id(+) = ol.line_id
and cis.instance_status_id(+) = cii.instance_status_id
and msib.organization_id = 108
and fu.user_id = oh.created_by
--and ol.line_id = :p_line_id


Upg 11666447
Base 11666042

SELECT * FROM XXVM_OKC_OCEF_TBL where status='NEW'

----------------------------------------------------------------------------------------------------------------------------------------------
Covered on VMWare SnS

Channel Change :The PAC received is associated with a different OEM Partner or is covered under VMware SnS. VMware is manually processing this renewal

select * from xxvm_okc_ocef_tbl where paclicense in ( '5H626-2CH36-L8T6E-9R1H4-04QMH');
select * from xxvm_okc_ocef_audit_tbl where paclicense in ( '5H626-2CH36-L8T6E-9R1H4-04QMH');
select * from xxvm_okc_ocef_audit_hist_tbl where paclicense in ( '5H626-2CH36-L8T6E-9R1H4-04QMH');
select end_date,last_update_date from okc_k_headers_b where contract_number='45453643' --'45453645'
select instance_status_id from csi_item_instances where instance_id=136271431




SELECT x.id contract_id,
x.contract_number,
x.inv_organization_id,
ood.operating_unit,
ib_line.id,
ib_line.end_date,
NVL(x.qcl_id,1),
osb.ste_code,
UPPER(coverage.name)
FROM okc_k_headers_b x,
okc_k_lines_b support_line,
okc_k_items support_item,
okc_k_lines_b ib_line,
okc_k_items ib_item,
org_organization_definitions ood,
okc_statuses_b osb,
oks_coverages_v coverage
WHERE support_line.ID = support_item.cle_id
AND support_item.jtot_object1_code = 'OKX_SERVICE'
AND support_line.ID = ib_line.cle_id
AND ib_line.ID = ib_item.cle_id
AND ib_item.jtot_object1_code = 'OKX_CUSTPROD'
AND x.ID = support_line.chr_id
AND ib_item.object1_id1 = TO_CHAR(143310607)
AND x.inv_organization_id = ood.organization_id
AND x.sts_code = osb.code
AND NVL(support_line.line_renewal_type_code,'FUL') <> 'DNR'
AND osb.ste_code IN ('ACTIVE','EXPIRED','SIGNED')
AND coverage.service_cle_id = support_line.id
--AND UPPER(coverage.name) = 'SUBSCRIPTION ONLY'
AND TRUNC(NVL(ib_line.date_terminated,sysdate+1)) > TRUNC(sysdate) --Adding for Upgrade SKUs
;

XXVM_OKC_EXCLUDE_BILL_TO

SELECT UPPER(description)
FROM fnd_lookup_values_vl
WHERE lookup_type = -------'XXVM_OCEF_EMAIL_DOM'
'XXVM_OKC_EXCLUDE_BILL_TO'


SELECT *
FROM fnd_lookuP_TYPES
WHERE lookup_type LIKE 'XXVM%OCEF%'
--'XXVM_OKC_EXCLUDE_BILL_TO'
-- XXVM_OCEF_EXCEP_CONF

XXVM_OCEF_EXCEP_CONF

Partial Upgrade + Channel Change Scenario. Products extended by VMware: Base SKU, Base Quantity Upgrade SKU, Upgrade Quantity


The end date provided is the same as the end date currently within VMware systems. No follow up action required.

FW: NEW - Defect # BUG-00069520: Verify the OCEF functionality and error messages for HB full upgrades via channel change.

select lookup_type,lookup_code,meaning,description from fnd_lookup_values where lookup_type = 'XXVM_OCEF_EXCEP_CONF';

select * from APPS.xxvm_okc_ocef_audit_hist_tbl --WHERE TRUNC(CREATION_DATE) BETWEEN TRUNC(SYSDATE)-10 AND TRUNC(SYSDATE)
a