[Home] [Help]
4: error EXCEPTION;
5:
6: /*** Globals ***/
7: g_object_name CONSTANT VARCHAR2(30) := 'CSL_SERVICE_REQUESTS_PKG';
8: g_pub_name CONSTANT VARCHAR2(30) := 'CSL_CS_INCIDENTS_ALL_VL';
9: g_debug_level NUMBER; -- debug level
10:
11: CURSOR c_incident( b_user_name VARCHAR2, b_tranid NUMBER) is
12: SELECT *
9: g_debug_level NUMBER; -- debug level
10:
11: CURSOR c_incident( b_user_name VARCHAR2, b_tranid NUMBER) is
12: SELECT *
13: FROM CSL_CS_INCIDENTS_ALL_VL_inq
14: WHERE tranid$$ = b_tranid
15: AND clid$$cs = b_user_name;
16:
17: CURSOR c_contact( b_incident_id NUMBER, b_tranid NUMBER, b_user_name VARCHAR2 ) IS
33:
34: -- Commented Out for Sql Repository Performance Fix
35:
36: /* CURSOR c_org_rel_contacts
37: ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
38: ) IS
39: SELECT b.party_id
40: FROM CSC_HZ_PARTIES_V b
41: WHERE b.object_id = b_customer_id
47: -- AND b.obj_party_type = 'ORGANIZATION' -- can be both org and person caller type
48: AND rownum <= 1;
49:
50: CURSOR c_person_contacts
51: ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
52: ) IS
53: SELECT b.subject_id
54: FROM CSC_HZ_PARTIES_V b
55: WHERE b.object_id = b_customer_id
64: AND status ='A'; */
65:
66:
67: CURSOR c_org_rel_contacts
68: ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
69: ) IS
70: SELECT b.party_id FROM HZ_RELATIONSHIPS b
71: WHERE b.object_id = b_customer_id
72: AND object_table_name = 'HZ_PARTIES'
75: AND rownum <= 1;
76:
77:
78: CURSOR c_person_contacts
79: ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
80: ) IS
81: SELECT b.subject_id FROM HZ_RELATIONSHIPS b
82: WHERE b.object_id = b_customer_id
83: AND object_table_name = 'HZ_PARTIES'
85: AND rownum <= 1;
86:
87:
88: CURSOR c_party_contact
89: ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
90: ) IS
91: SELECT PARTY_ID FROM HZ_PARTIES
92: WHERE party_id = b_customer_id AND status = 'A';
93:
94:
95: /* ER 3746707
96: Cursor to get the Bill to Address */
97: CURSOR c_bill_to_site_id
98: ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
99: ) IS
100: SELECT use.party_site_use_id FROM
101: hz_party_sites site, hz_party_site_uses use
102: WHERE site.party_site_id = use.party_site_id
110:
111: /* ER 3746707
112: Cursor to get the Ship to Address */
113: CURSOR c_ship_to_site_id
114: ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
115: ) IS
116: SELECT use.party_site_use_id FROM
117: hz_party_sites site, hz_party_site_uses use
118: WHERE site.party_site_id = use.party_site_id
126:
127: /* ER 3746707
128: Cursor to get the customer account id */
129: CURSOR c_customer_account
130: ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
131: ) IS
132: SELECT custa.cust_account_id FROM hz_cust_accounts custa
133: WHERE custa.status = 'A'
134: AND custa.party_id = b_customer_id;
438: )
439: is
440: select incident_id
441: , object_version_number
442: from cs_incidents
443: where incident_id = b_incident_id;
444:
445: r_ovn c_ovn%rowtype;
446:
448: ( b_incident_id NUMBER
449: )
450: is
451: SELECT LAST_UPDATE_DATE
452: from CS_INCIDENTS_ALL_B
453: where incident_id = b_incident_id;
454:
455: r_last_update_date c_last_update_date%ROWTYPE;
456:
460: is
461: SELECT CREDIT_CARD_NUMBER, CREDIT_CARD_TYPE_CODE,
462: CREDIT_CARD_EXPIRATION_DATE, CREDIT_CARD_HOLDER_FNAME,
463: CREDIT_CARD_HOLDER_MNAME, CREDIT_CARD_HOLDER_LNAME, CREDIT_CARD_ID
464: FROM CS_INCIDENTS_ALL_B
465: WHERE incident_id = b_incident_id;
466:
467: r_credit_card c_credit_card%ROWTYPE;
468:
813: END IF;
814:
815: IF p_record.dmltype$$ = 'U' AND x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
816: l_access_id := jtm_hook_util_pkg.get_acc_id(
817: p_acc_table_name => 'CSL_CS_INCIDENTS_ALL_ACC',
818: p_resource_id => JTM_HOOK_UTIL_PKG.get_resource_id( p_record.clid$$cs ),
819: p_pk1_name => 'INCIDENT_ID',
820: p_pk1_num_value => p_record.INCIDENT_ID
821: );
862: x_return_status := FND_API.G_RET_STS_ERROR;
863: END APPLY_RECORD;
864:
865: /***
866: This procedure is called by CSL_SERVICEL_WRAPPER_PKG when publication item CSL_CS_INCIDENTS_ALL_VL
867: is dirty. This happens when a mobile field service device executed DML on an updatable table and did
868: a fast sync. This procedure will insert the data that came from mobile into the backend tables using
869: public APIs.
870: ***/
889: , v_message => 'Entering ' || g_object_name || '.Apply_Client_Changes'
890: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
891: END IF;
892:
893: /*** loop through CSL_CS_INCIDENTS_ALL_VL records in inqueue ***/
894: FOR r_incident IN c_incident( p_user_name, p_tranid) LOOP
895:
896: /*** apply record ***/
897: APPLY_RECORD