eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAP_CMERGE dependencies on PA_PROJECT_CUSTOMERS

Line 5: -- function to retrieve new customer id for pa_project_customers table

1: PACKAGE BODY PAP_CMERGE AS
2: -- $Header: PAPCMR3B.pls 120.2.12010000.4 2008/08/29 14:26:45 arbandyo ship $
3: /* */
4: --
5: -- function to retrieve new customer id for pa_project_customers table
6: -- using current customer id only.
7: --
8: FUNCTION RETRIEVE_CUSTOMER_ID( set_no IN NUMBER ) RETURN BOOLEAN IS
9: BEGIN

Line 27: -- exist in pa_project_customers.

23: RETURN TRUE;
24: END RETRIEVE_CUSTOMER_ID;
25: --
26: -- function to verify whether duplicate index record ( project_id + customer_id )
27: -- exist in pa_project_customers.
28: --
29: FUNCTION CHECK_DUPLICATE_INDEX RETURN BOOLEAN IS
30: BEGIN
31: SELECT CUSTOMER_BILL_SPLIT

Line 33: FROM PA_PROJECT_CUSTOMERS PC

29: FUNCTION CHECK_DUPLICATE_INDEX RETURN BOOLEAN IS
30: BEGIN
31: SELECT CUSTOMER_BILL_SPLIT
32: INTO new_customer_bill_split
33: FROM PA_PROJECT_CUSTOMERS PC
34: WHERE PC.PROJECT_ID = var_project_id
35: AND PC.CUSTOMER_ID = new_customer_id;
36: RETURN FALSE;
37: EXCEPTION

Line 51: FROM pa_project_customers PA,RA_CUSTOMER_MERGES RACM

47: PROCEDURE MERGE ( req_id IN NUMBER, set_no IN NUMBER, process_mode IN VARCHAR2 ) IS
48: --
49: /* CURSOR cursor_1 IS SELECT DISTINCT PA.PROJECT_ID, PA.CUSTOMER_ID, PA.BILL_TO_ADDRESS_ID,
50: PA.SHIP_TO_ADDRESS_ID,PA.BILL_TO_CUSTOMER_ID,PA.SHIP_TO_CUSTOMER_ID, DEFAULT_TOP_TASK_CUST_FLAG -- FP_M Changes
51: FROM pa_project_customers PA,RA_CUSTOMER_MERGES RACM
52: WHERE RACM.PROCESS_FLAG = 'N'
53: AND RACM.SET_NUMBER = set_no
54: AND (PA.CUSTOMER_ID=RACM.DUPLICATE_ID
55: OR PA.BILL_TO_CUSTOMER_ID=RACM.DUPLICATE_ID

Line 65: FROM pa_project_customers PA

61:
62: /* Modified cursor for bug 3938428*/
63: CURSOR cursor_1 IS SELECT PA.PROJECT_ID, PA.CUSTOMER_ID, PA.BILL_TO_ADDRESS_ID,
64: PA.SHIP_TO_ADDRESS_ID,PA.BILL_TO_CUSTOMER_ID,PA.SHIP_TO_CUSTOMER_ID , DEFAULT_TOP_TASK_CUST_FLAG -- FP_M Changes
65: FROM pa_project_customers PA
66: WHERE EXISTS (
67: SELECT 1 FROM RA_CUSTOMER_MERGES RACM
68: WHERE RACM.PROCESS_FLAG = 'N'
69: AND RACM.SET_NUMBER = set_no

Line 84: SELECT CUSTOMER_ID FROM pa_project_customers PC

80: WHERE PROCESS_FLAG = 'N'
81: AND SET_NUMBER = set_no;
82: --
83: CURSOR cursor_lock_1 IS
84: SELECT CUSTOMER_ID FROM pa_project_customers PC
85: WHERE
86: PC.CUSTOMER_ID IN ( SELECT DISTINCT RACM.DUPLICATE_ID
87: FROM RA_CUSTOMER_MERGES RACM
88: WHERE RACM.PROCESS_FLAG = 'N'

Line 166: var_bill_to_address_id pa_project_customers.bill_to_address_id%TYPE;

162: WHERE RACM.PROCESS_FLAG = 'N'
163: AND RACM.SET_NUMBER = set_no )
164: FOR UPDATE NOWAIT;
165: --
166: var_bill_to_address_id pa_project_customers.bill_to_address_id%TYPE;
167: var_ship_to_address_id pa_project_customers.ship_to_address_id%TYPE;
168: var_bill_to_cust_id pa_project_customers.bill_to_customer_id%TYPE;
169: var_ship_to_cust_id pa_project_customers.ship_to_customer_id%TYPE;
170: var_Default_Top_Task_Cust_Flag pa_project_customers.DEFAULT_TOP_TASK_CUST_FLAG%TYPE;

Line 167: var_ship_to_address_id pa_project_customers.ship_to_address_id%TYPE;

163: AND RACM.SET_NUMBER = set_no )
164: FOR UPDATE NOWAIT;
165: --
166: var_bill_to_address_id pa_project_customers.bill_to_address_id%TYPE;
167: var_ship_to_address_id pa_project_customers.ship_to_address_id%TYPE;
168: var_bill_to_cust_id pa_project_customers.bill_to_customer_id%TYPE;
169: var_ship_to_cust_id pa_project_customers.ship_to_customer_id%TYPE;
170: var_Default_Top_Task_Cust_Flag pa_project_customers.DEFAULT_TOP_TASK_CUST_FLAG%TYPE;
171: not_found_flag BOOLEAN;

Line 168: var_bill_to_cust_id pa_project_customers.bill_to_customer_id%TYPE;

164: FOR UPDATE NOWAIT;
165: --
166: var_bill_to_address_id pa_project_customers.bill_to_address_id%TYPE;
167: var_ship_to_address_id pa_project_customers.ship_to_address_id%TYPE;
168: var_bill_to_cust_id pa_project_customers.bill_to_customer_id%TYPE;
169: var_ship_to_cust_id pa_project_customers.ship_to_customer_id%TYPE;
170: var_Default_Top_Task_Cust_Flag pa_project_customers.DEFAULT_TOP_TASK_CUST_FLAG%TYPE;
171: not_found_flag BOOLEAN;
172: records_locked EXCEPTION;

Line 169: var_ship_to_cust_id pa_project_customers.ship_to_customer_id%TYPE;

165: --
166: var_bill_to_address_id pa_project_customers.bill_to_address_id%TYPE;
167: var_ship_to_address_id pa_project_customers.ship_to_address_id%TYPE;
168: var_bill_to_cust_id pa_project_customers.bill_to_customer_id%TYPE;
169: var_ship_to_cust_id pa_project_customers.ship_to_customer_id%TYPE;
170: var_Default_Top_Task_Cust_Flag pa_project_customers.DEFAULT_TOP_TASK_CUST_FLAG%TYPE;
171: not_found_flag BOOLEAN;
172: records_locked EXCEPTION;
173: total_record_upd_count NUMBER := 0;

Line 170: var_Default_Top_Task_Cust_Flag pa_project_customers.DEFAULT_TOP_TASK_CUST_FLAG%TYPE;

166: var_bill_to_address_id pa_project_customers.bill_to_address_id%TYPE;
167: var_ship_to_address_id pa_project_customers.ship_to_address_id%TYPE;
168: var_bill_to_cust_id pa_project_customers.bill_to_customer_id%TYPE;
169: var_ship_to_cust_id pa_project_customers.ship_to_customer_id%TYPE;
170: var_Default_Top_Task_Cust_Flag pa_project_customers.DEFAULT_TOP_TASK_CUST_FLAG%TYPE;
171: not_found_flag BOOLEAN;
172: records_locked EXCEPTION;
173: total_record_upd_count NUMBER := 0;
174: total_record_del_count NUMBER := 0;

Line 201: ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_PROJECT_CUSTOMERS', FALSE );

197: --
198: IF process_mode = 'LOCK' THEN
199: --
200: ARP_MESSAGE.SET_NAME( 'AR', 'AR_LOCKING_TABLE' );
201: ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_PROJECT_CUSTOMERS', FALSE );
202: --
203: open cursor_lock_1;
204: close cursor_lock_1;
205: --

Line 260: ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_PROJECT_CUSTOMERS' );

256: --
257: END IF;
258: --
259: ARP_MESSAGE.SET_NAME( 'AR', 'AR_UPDATING_TABLE' );
260: ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_PROJECT_CUSTOMERS' );
261: --
262: /* Added for Bug 3891382. The logic prevents deletion of the customer being
263: merged, if the customer is having sites in single org unit and is being
264: referenced in other org units. */

Line 276: SELECT count(*) INTO var_count1 FROM pa_project_customers

272: EXIT WHEN cursor_2%NOTFOUND;
273:
274: IF var_dup_flag = 'Y' and var_cust_id <> var_dup_id THEN /* for bug 6732730*/
275: /* Modified to var_cust_id for bug 7341412 */
276: SELECT count(*) INTO var_count1 FROM pa_project_customers
277: WHERE customer_id = var_dup_id
278: AND project_id NOT IN (
279: SELECT project_id FROM pa_projects);
280:

Line 317: -- this loop fetches each row from pa_project_customers table and verifies whether

313:
314: OPEN cursor_1;
315: --
316: --
317: -- this loop fetches each row from pa_project_customers table and verifies whether
318: -- duplicate index ( project_id + customer_id ) exist and if it does then it would
319: -- delete the duplicate index row and add the customer bill split to the updated
320: -- row so that sum of the customer bill split for all the customers for that project
321: -- equals 100%.

Line 325: -- fetch each row from pa_project_customers table.

321: -- equals 100%.
322: --
323: LOOP
324: --
325: -- fetch each row from pa_project_customers table.
326: --
327: FETCH cursor_1 INTO var_project_id, var_customer_id,
328: var_bill_to_address_id,
329: var_ship_to_address_id,

Line 351: FROM PA_PROJECT_CUSTOMERS PC

347: not_found_flag := CHECK_DUPLICATE_INDEX;
348:
349: SELECT CUSTOMER_BILL_SPLIT
350: INTO var_customer_bill_split
351: FROM PA_PROJECT_CUSTOMERS PC
352: WHERE PC.PROJECT_ID = var_project_id
353: AND PC.CUSTOMER_ID = old_customer_id;
354:
355: /*Bug5462389*/

Line 408: 'PA_PROJECT_CUSTOMERS',

404: DEL_COL24 -- FP_M Changes
405: )
406: ( SELECT
407: HZ_CUSTOMER_MERGE_LOG_s.nextval,
408: 'PA_PROJECT_CUSTOMERS',
409: RACM.CUSTOMER_MERGE_HEADER_ID,
410: var_project_id,
411: new_customer_id,
412: 'D',

Line 443: FROM PA_PROJECT_CUSTOMERS pc,ra_customer_merges RACM

439: ,PC.RETENTION_LEVEL_CODE
440: ,PC.BILL_TO_CUSTOMER_ID
441: ,PC.SHIP_TO_CUSTOMER_ID
442: ,PC.Default_Top_Task_Cust_Flag -- FP_M Changes
443: FROM PA_PROJECT_CUSTOMERS pc,ra_customer_merges RACM
444: WHERE RACM.DUPLICATE_ID = var_customer_id
445: AND PC.PROJECT_ID=var_project_id
446: AND PC.CUSTOMER_ID = var_customer_id
447: AND RACM.PROCESS_FLAG = 'N'

Line 456: DELETE FROM pa_project_customers PC

452: --
453: -- if duplicate index row exist then we need to delete this row
454: -- and update the customer bill split accordingly.
455: --
456: DELETE FROM pa_project_customers PC
457: WHERE PC.PROJECT_ID = var_project_id
458: AND PC.CUSTOMER_ID = var_customer_id;
459: --
460: total_record_del_count := total_record_del_count + 1;

Line 483: 'PA_PROJECT_CUSTOMERS',

479: LAST_UPDATED_BY
480: )
481: ( SELECT
482: HZ_CUSTOMER_MERGE_LOG_s.nextval,
483: 'PA_PROJECT_CUSTOMERS',
484: RACM.CUSTOMER_MERGE_HEADER_ID,
485: PC.project_id,
486: PC.customer_id,
487: PC.customer_bill_split,

Line 496: FROM PA_PROJECT_CUSTOMERS PC, ra_customer_merges RACM

492: hz_utility_pub.CREATION_DATE,
493: hz_utility_pub.LAST_UPDATE_LOGIN,
494: hz_utility_pub.LAST_UPDATE_DATE,
495: hz_utility_pub.LAST_UPDATED_BY
496: FROM PA_PROJECT_CUSTOMERS PC, ra_customer_merges RACM
497: WHERE PC.PROJECT_ID = var_project_id
498: AND PC.CUSTOMER_ID = new_customer_id
499: AND RACM.DUPLICATE_ID = var_customer_id
500: AND RACM.PROCESS_FLAG = 'N'

Line 507: UPDATE pa_project_customers PC

503:
504: END IF;
505: /* End of Tca audit*/
506:
507: UPDATE pa_project_customers PC
508: SET PC.CUSTOMER_BILL_SPLIT = PC.CUSTOMER_BILL_SPLIT +
509: var_customer_bill_split
510: WHERE
511: PC.PROJECT_ID = var_project_id

Line 520: UPDATE pa_project_customers PC

516: -- the Default_Top_task_Cust_Flag column also in Project Customers table
517: -- and
518: -- update the customer with new customer id in Project Tasks table
519: If var_Default_Top_Task_Cust_Flag = 'Y' then
520: UPDATE pa_project_customers PC
521: SET PC.Default_Top_Task_Cust_Flag = 'Y'
522: WHERE PC.PROJECT_ID = var_project_id
523: AND PC.CUSTOMER_ID = new_customer_id;
524: End IF; /* Added for bug 4218767*/

Line 530: -- update pa_project_customers table for bill to address id, this is necessary

526: /*Bug5462389:moved the update on pa_tasks before the IF condition*/
527: -- End of FP_M Changes
528: ELSE
529: --
530: -- update pa_project_customers table for bill to address id, this is necessary
531: -- because if we update customer id also then we would not be able to update
532: -- ship to address id.
533: --
534:

Line 555: 'PA_PROJECT_CUSTOMERS',

551: LAST_UPDATE_DATE,
552: LAST_UPDATED_BY)
553: ( SELECT
554: HZ_CUSTOMER_MERGE_LOG_s.nextval,
555: 'PA_PROJECT_CUSTOMERS',
556: RACM.CUSTOMER_MERGE_HEADER_ID,
557: PC.project_id,
558: PC.customer_id,
559: PC.bill_to_address_id,

Line 568: FROM PA_PROJECT_CUSTOMERS PC, RA_CUSTOMER_MERGES RACM

564: hz_utility_pub.CREATION_DATE,
565: hz_utility_pub.LAST_UPDATE_LOGIN,
566: hz_utility_pub.LAST_UPDATE_DATE,
567: hz_utility_pub.LAST_UPDATED_BY
568: FROM PA_PROJECT_CUSTOMERS PC, RA_CUSTOMER_MERGES RACM
569: WHERE RACM.DUPLICATE_ID = var_bill_to_cust_id /*CACR*/
570: AND RACM.DUPLICATE_ADDRESS_ID = PC.BILL_TO_ADDRESS_ID
571: AND RACM.PROCESS_FLAG = 'N'
572: AND RACM.SET_NUMBER = set_no

Line 580: UPDATE pa_project_customers PC

576:
577: END IF;
578: /* End of TCA audit*/
579:
580: UPDATE pa_project_customers PC
581: SET ( BILL_TO_ADDRESS_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ADDRESS_ID
582: FROM RA_CUSTOMER_MERGES RACM
583: WHERE RACM.DUPLICATE_ID = var_bill_to_cust_id /*CACR*/
584: AND RACM.DUPLICATE_ADDRESS_ID = PC.BILL_TO_ADDRESS_ID

Line 601: -- update pa_project_customers table for customer_id and ship_to_address_id.

597: AND RACM.CUSTOMER_SITE_CODE = 'BILL_TO' )
598: AND PC.PROJECT_ID = var_project_id
599: AND PC.CUSTOMER_ID = var_customer_id;
600: --
601: -- update pa_project_customers table for customer_id and ship_to_address_id.
602: --
603: IF g_audit_profile='Y' THEN
604:
605: INSERT INTO HZ_CUSTOMER_MERGE_LOG (

Line 622: 'PA_PROJECT_CUSTOMERS',

618: LAST_UPDATE_DATE,
619: LAST_UPDATED_BY)
620: ( SELECT
621: HZ_CUSTOMER_MERGE_LOG_s.nextval,
622: 'PA_PROJECT_CUSTOMERS',
623: RACM.CUSTOMER_MERGE_HEADER_ID,
624: PC.project_id,
625: PC.customer_id,
626: PC.ship_to_address_id,

Line 635: FROM PA_PROJECT_CUSTOMERS PC, RA_CUSTOMER_MERGES RACM

631: hz_utility_pub.CREATION_DATE,
632: hz_utility_pub.LAST_UPDATE_LOGIN,
633: hz_utility_pub.LAST_UPDATE_DATE,
634: hz_utility_pub.LAST_UPDATED_BY
635: FROM PA_PROJECT_CUSTOMERS PC, RA_CUSTOMER_MERGES RACM
636: WHERE RACM.DUPLICATE_ID = var_ship_to_cust_id /*CACR*/
637: AND RACM.DUPLICATE_ADDRESS_ID = PC.SHIP_TO_ADDRESS_ID
638: AND RACM.PROCESS_FLAG = 'N'
639: AND RACM.SET_NUMBER = set_no

Line 646: UPDATE pa_project_customers PC

642: AND PC.CUSTOMER_ID = var_customer_id);
643:
644: END IF;
645:
646: UPDATE pa_project_customers PC
647: SET ( SHIP_TO_ADDRESS_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ADDRESS_ID
648: FROM RA_CUSTOMER_MERGES RACM
649: WHERE RACM.DUPLICATE_ID = var_ship_to_cust_id /*CACR*/
650: AND RACM.DUPLICATE_ADDRESS_ID = PC.SHIP_TO_ADDRESS_ID

Line 688: 'PA_PROJECT_CUSTOMERS',

684: LAST_UPDATE_DATE,
685: LAST_UPDATED_BY)
686: ( SELECT
687: HZ_CUSTOMER_MERGE_LOG_s.nextval,
688: 'PA_PROJECT_CUSTOMERS',
689: RACM.CUSTOMER_MERGE_HEADER_ID,
690: PC.project_id,
691: PC.customer_id,
692: PC.bill_to_customer_id,

Line 701: FROM PA_PROJECT_CUSTOMERS PC, RA_CUSTOMER_MERGES RACM

697: hz_utility_pub.CREATION_DATE,
698: hz_utility_pub.LAST_UPDATE_LOGIN,
699: hz_utility_pub.LAST_UPDATE_DATE,
700: hz_utility_pub.LAST_UPDATED_BY
701: FROM PA_PROJECT_CUSTOMERS PC, RA_CUSTOMER_MERGES RACM
702: WHERE RACM.DUPLICATE_ID = var_bill_to_cust_id /*CACR*/
703: AND RACM.PROCESS_FLAG = 'N'
704: AND RACM.SET_NUMBER = set_no
705: AND PC.PROJECT_ID = var_project_id

Line 711: UPDATE pa_project_customers PC

707: AND RACM.CUSTOMER_ID<>RACM.DUPLICATE_ID
708: AND ROWNUM=1);
709: END IF;
710:
711: UPDATE pa_project_customers PC
712: SET ( BILL_TO_CUSTOMER_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ID
713: FROM RA_CUSTOMER_MERGES RACM
714: WHERE RACM.DUPLICATE_ID = var_bill_to_cust_id
715: AND RACM.PROCESS_FLAG = 'N'

Line 747: 'PA_PROJECT_CUSTOMERS',

743: LAST_UPDATE_DATE,
744: LAST_UPDATED_BY)
745: ( SELECT
746: HZ_CUSTOMER_MERGE_LOG_s.nextval,
747: 'PA_PROJECT_CUSTOMERS',
748: RACM.CUSTOMER_MERGE_HEADER_ID,
749: PC.project_id,
750: PC.customer_id,
751: PC.ship_to_customer_id,

Line 760: FROM PA_PROJECT_CUSTOMERS PC, RA_CUSTOMER_MERGES RACM

756: hz_utility_pub.CREATION_DATE,
757: hz_utility_pub.LAST_UPDATE_LOGIN,
758: hz_utility_pub.LAST_UPDATE_DATE,
759: hz_utility_pub.LAST_UPDATED_BY
760: FROM PA_PROJECT_CUSTOMERS PC, RA_CUSTOMER_MERGES RACM
761: WHERE RACM.DUPLICATE_ID = var_ship_to_cust_id /*CACR*/
762: AND RACM.PROCESS_FLAG = 'N'
763: AND RACM.SET_NUMBER = set_no
764: AND PC.PROJECT_ID = var_project_id

Line 770: UPDATE pa_project_customers PC

766: AND RACM.CUSTOMER_ID<>RACM.DUPLICATE_ID
767: AND ROWNUM=1);
768: END IF;
769:
770: UPDATE pa_project_customers PC
771: SET ( SHIP_TO_CUSTOMER_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ID
772: FROM RA_CUSTOMER_MERGES RACM
773: WHERE RACM.DUPLICATE_ID = var_ship_to_cust_id
774: AND RACM.PROCESS_FLAG = 'N'

Line 787: -- update pa_project_customers for customer_id only, this is necessary because

783: AND RACM.SET_NUMBER = set_no )
784: AND PC.PROJECT_ID = var_project_id
785: AND PC.CUSTOMER_ID = var_customer_id;
786: --
787: -- update pa_project_customers for customer_id only, this is necessary because
788: -- if in the previous update if ship_to_address_id is null then customer_id
789: -- would not get updated.
790: --
791:

Line 810: 'PA_PROJECT_CUSTOMERS',

806: LAST_UPDATE_LOGIN,
807: LAST_UPDATE_DATE,
808: LAST_UPDATED_BY)
809: ( select HZ_CUSTOMER_MERGE_LOG_s.nextval,
810: 'PA_PROJECT_CUSTOMERS',
811: RACM.CUSTOMER_MERGE_HEADER_ID,
812: PC.project_id,
813: RACM.CUSTOMER_ID,
814: PC.customer_id,

Line 823: FROM PA_PROJECT_CUSTOMERS PC, ra_customer_merges RACM

819: hz_utility_pub.CREATION_DATE,
820: hz_utility_pub.LAST_UPDATE_LOGIN,
821: hz_utility_pub.LAST_UPDATE_DATE,
822: hz_utility_pub.LAST_UPDATED_BY
823: FROM PA_PROJECT_CUSTOMERS PC, ra_customer_merges RACM
824: WHERE RACM.DUPLICATE_ID = var_customer_id
825: AND RACM.PROCESS_FLAG = 'N'
826: AND RACM.SET_NUMBER = set_no
827: AND PC.PROJECT_ID = var_project_id

Line 833: UPDATE pa_project_customers PC

829: AND RACM.CUSTOMER_ID<>RACM.DUPLICATE_ID
830: AND ROWNUM=1 );
831:
832: END IF;
833: UPDATE pa_project_customers PC
834: SET ( CUSTOMER_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ID
835: FROM RA_CUSTOMER_MERGES RACM
836: WHERE RACM.DUPLICATE_ID = var_customer_id
837: AND RACM.PROCESS_FLAG = 'N'