DBA Data[Home] [Help]

APPS.FII_AP_INV_SUM_INIT dependencies on FII_AP_HOLD_HIST_B

Line 6478: g_state := 'Deleting records from FII_AP_HOLD_HIST_B that are already existing';

6474: FII_UTIL.put_line('');
6475: FII_UTIL.put_line(g_state);
6476: end if;
6477:
6478: g_state := 'Deleting records from FII_AP_HOLD_HIST_B that are already existing';
6479: if g_debug_flag = 'Y' then
6480: FII_UTIL.put_line('');
6481: FII_UTIL.put_line(g_state);
6482: end if;

Line 6487: TRUNCATE_TABLE('MLOG$_FII_AP_HOLD_HIST_B');

6483:
6484:
6485: /* For Initial Load we will truncate the data in the hold history table
6486: and re-populate this table */
6487: TRUNCATE_TABLE('MLOG$_FII_AP_HOLD_HIST_B');
6488: TRUNCATE_TABLE('FII_AP_HOLD_HIST_B');
6489:
6490:
6491: g_state := 'Populating FII_AP_HOLD_HIST_B FROM AP_HOLDS_ALL table';

Line 6488: TRUNCATE_TABLE('FII_AP_HOLD_HIST_B');

6484:
6485: /* For Initial Load we will truncate the data in the hold history table
6486: and re-populate this table */
6487: TRUNCATE_TABLE('MLOG$_FII_AP_HOLD_HIST_B');
6488: TRUNCATE_TABLE('FII_AP_HOLD_HIST_B');
6489:
6490:
6491: g_state := 'Populating FII_AP_HOLD_HIST_B FROM AP_HOLDS_ALL table';
6492: if g_debug_flag = 'Y' then

Line 6491: g_state := 'Populating FII_AP_HOLD_HIST_B FROM AP_HOLDS_ALL table';

6487: TRUNCATE_TABLE('MLOG$_FII_AP_HOLD_HIST_B');
6488: TRUNCATE_TABLE('FII_AP_HOLD_HIST_B');
6489:
6490:
6491: g_state := 'Populating FII_AP_HOLD_HIST_B FROM AP_HOLDS_ALL table';
6492: if g_debug_flag = 'Y' then
6493: FII_UTIL.put_line(g_state);
6494: end if;
6495:

Line 6514: INSERT /*+ append parallel(HH) */ INTO FII_AP_HOLD_HIST_B HH

6510: this by checking if this hold is the very last to be released or if all holds
6511: released after it were held after this hold was released.
6512: */
6513:
6514: INSERT /*+ append parallel(HH) */ INTO FII_AP_HOLD_HIST_B HH
6515: (TIME_ID,
6516: PERIOD_TYPE_ID,
6517: ORG_ID,
6518: SUPPLIER_ID,

Line 6533: DECODE(REC_TYPE, 'H', FII_AP_HOLD_HIST_B_S.NEXTVAL, NULL),

6529: 1,
6530: ORG_ID,
6531: SUPPLIER_ID,
6532: INVOICE_ID,
6533: DECODE(REC_TYPE, 'H', FII_AP_HOLD_HIST_B_S.NEXTVAL, NULL),
6534: REC_TYPE,
6535: H_R_DATE,
6536: g_fii_user_id CREATED_BY,
6537: sysdate CREATION_DATE,

Line 6598: FII_UTIL.put_line('Inserted '|| SQL%ROWCOUNT ||' Hold and Release records into FII_AP_HOLD_HIST_B');

6594: AND AI.Invoice_Type NOT IN ('PREPAYMENT'));
6595:
6596:
6597: if g_debug_flag = 'Y' then
6598: FII_UTIL.put_line('Inserted '|| SQL%ROWCOUNT ||' Hold and Release records into FII_AP_HOLD_HIST_B');
6599: FII_UTIL.stop_timer;
6600: FII_UTIL.print_timer('Duration');
6601: FII_UTIL.put_line('');
6602: end if;

Line 6613: UPDATE FII_AP_Hold_Hist_B HH

6609: FII_UTIL.start_timer;
6610: FII_UTIL.put_line('');
6611: end if;
6612:
6613: UPDATE FII_AP_Hold_Hist_B HH
6614: SET Seq_ID = (SELECT HH1.Seq_ID
6615: FROM FII_AP_Hold_Hist_B HH1
6616: WHERE HH1.Action = 'H'
6617: AND HH1.Invoice_ID = HH.Invoice_ID

Line 6615: FROM FII_AP_Hold_Hist_B HH1

6611: end if;
6612:
6613: UPDATE FII_AP_Hold_Hist_B HH
6614: SET Seq_ID = (SELECT HH1.Seq_ID
6615: FROM FII_AP_Hold_Hist_B HH1
6616: WHERE HH1.Action = 'H'
6617: AND HH1.Invoice_ID = HH.Invoice_ID
6618: AND HH1.Action_Date IN
6619: (SELECT MIN(TRUNC(AH1.Hold_Date))

Line 6634: FII_UTIL.put_line('Updated '|| SQL%ROWCOUNT ||' Release records in the FII_AP_HOLD_HIST_B');

6630: AND HH.Seq_ID IS NULL;
6631:
6632:
6633: if g_debug_flag = 'Y' then
6634: FII_UTIL.put_line('Updated '|| SQL%ROWCOUNT ||' Release records in the FII_AP_HOLD_HIST_B');
6635: FII_UTIL.stop_timer;
6636: FII_UTIL.print_timer('Duration');
6637: FII_UTIL.put_line('');
6638: end if;

Line 6650: UPDATE FII_AP_Hold_Hist_B HH

6646: FII_UTIL.start_timer;
6647: FII_UTIL.put_line('');
6648: end if;
6649:
6650: UPDATE FII_AP_Hold_Hist_B HH
6651: SET Hold_Count = (SELECT DECODE(HH.Action,'H', COUNT(*), -1 * COUNT(*))
6652: FROM AP_Holds_ALL AH
6653: WHERE AH.Invoice_ID = HH.Invoice_ID
6654: AND (EXISTS (SELECT 'Hold Exists'

Line 6655: FROM FII_AP_Hold_Hist_B HH1

6651: SET Hold_Count = (SELECT DECODE(HH.Action,'H', COUNT(*), -1 * COUNT(*))
6652: FROM AP_Holds_ALL AH
6653: WHERE AH.Invoice_ID = HH.Invoice_ID
6654: AND (EXISTS (SELECT 'Hold Exists'
6655: FROM FII_AP_Hold_Hist_B HH1
6656: WHERE HH1.Invoice_ID = AH.Invoice_ID
6657: AND HH1.Seq_ID = HH.Seq_ID
6658: AND TRUNC(AH.Hold_Date) >= DECODE(HH.Action,'H',
6659: HH.Action_Date, HH1.Action_Date)

Line 6666: FROM FII_AP_Hold_Hist_B HH2

6662: DECODE(HH.Action,'H',HH1.Action_Date,
6663: HH.Action_Date)
6664: AND HH1.Rowid <> HH.Rowid)
6665: OR NOT EXISTS (SELECT 'Release Exists'
6666: FROM FII_AP_Hold_Hist_B HH2
6667: WHERE HH2.Invoice_ID = AH.Invoice_ID
6668: AND HH.Seq_ID = HH2.Seq_ID
6669: AND HH2.Rowid <> HH.Rowid)))
6670: WHERE HH.Hold_Count IS NULL;

Line 6674: FII_UTIL.put_line('Updated '|| SQL%ROWCOUNT ||' Hold Counts in the FII_AP_HOLD_HIST_B');

6670: WHERE HH.Hold_Count IS NULL;
6671:
6672:
6673: if g_debug_flag = 'Y' then
6674: FII_UTIL.put_line('Updated '|| SQL%ROWCOUNT ||' Hold Counts in the FII_AP_HOLD_HIST_B');
6675: FII_UTIL.stop_timer;
6676: FII_UTIL.print_timer('Duration');
6677: FII_UTIL.put_line('');
6678: end if;