[Home] [Help]
185: --Aging_Bkts_B_Type is used to populate the memory structure FII_AP_Aging_Bkts_B_MS
186: TYPE Aging_Bkts_B_Type IS TABLE OF FII_AP_AGING_BKTS_B%ROWTYPE
187: INDEX BY BINARY_INTEGER;
188:
189: --Due_Counts_B_Type is used to populate the memory structure FII_AP_Due_Counts_B_MS
190: TYPE Due_Counts_B_Type IS TABLE OF FII_AP_DUE_COUNTS_B%ROWTYPE
191: INDEX BY BINARY_INTEGER;
192:
193: --Used to hold deleted records from FII_AP_Pay_Sched_B.
186: TYPE Aging_Bkts_B_Type IS TABLE OF FII_AP_AGING_BKTS_B%ROWTYPE
187: INDEX BY BINARY_INTEGER;
188:
189: --Due_Counts_B_Type is used to populate the memory structure FII_AP_Due_Counts_B_MS
190: TYPE Due_Counts_B_Type IS TABLE OF FII_AP_DUE_COUNTS_B%ROWTYPE
191: INDEX BY BINARY_INTEGER;
192:
193: --Used to hold deleted records from FII_AP_Pay_Sched_B.
194: TYPE Pay_Sched_D_Rec IS RECORD(
221: FII_AP_Inv_Pay_MS Inv_Pay_Type;
222: FII_AP_WH_Tax_MS WH_Tax_Type;
223: FII_AP_Prepay_Applied_MS Prepay_Applied_Type;
224: FII_AP_Aging_Bkts_B_MS Aging_Bkts_B_Type;
225: FII_AP_Due_Counts_B_MS Due_Counts_B_Type;
226: l_pay_sched_marker BINARY_INTEGER;
227: l_inv_pay_marker BINARY_INTEGER;
228: l_wh_tax_marker BINARY_INTEGER;
229: l_ps_wh_tax_marker BINARY_INTEGER; --Used to store the first WH/Tax for a payment schedule.
663: FII_UTIL.put_line('');
664: end if;
665:
666:
667: g_state := 'Deleting records from the FII_AP_DUE_COUNTS_B table';
668: if g_debug_flag = 'Y' then
669: FII_UTIL.put_line('');
670: FII_UTIL.put_line(g_state);
671: end if;
669: FII_UTIL.put_line('');
670: FII_UTIL.put_line(g_state);
671: end if;
672:
673: DELETE FROM FII_AP_DUE_COUNTS_B
674: WHERE Invoice_ID IN (SELECT Key_Value1_Num
675: FROM FII_AP_DBI_LOG_T
676: WHERE Table_Name = 'AP_INVOICES'
677: AND Operation_Flag = 'D');
676: WHERE Table_Name = 'AP_INVOICES'
677: AND Operation_Flag = 'D');
678:
679: if g_debug_flag = 'Y' then
680: FII_UTIL.put_line('Deleted '|| SQL%ROWCOUNT ||' records from the FII_AP_DUE_COUNTS_B');
681: FII_UTIL.put_line('');
682: end if;
683:
684:
1790: if g_debug_flag = 'Y' then
1791: FII_UTIL.put_line('The time taken to bulk insert into FII_AP_Aging_Bkts_B is: ' || to_char(l_timestamp1_tmp/100) || ' seconds.');
1792: end if;
1793:
1794: g_state := 'Bulk inserting into FII_AP_Due_Counts_B from FII_AP_Due_Counts_B_MS.';
1795: l_timestamp1_tmp := DBMS_UTILITY.Get_Time;
1796:
1797: IF FII_AP_Due_Counts_B_MS.Count > 0 THEN
1798: FORALL i IN FII_AP_Due_Counts_B_MS.First..FII_AP_Due_Counts_B_MS.Last
1793:
1794: g_state := 'Bulk inserting into FII_AP_Due_Counts_B from FII_AP_Due_Counts_B_MS.';
1795: l_timestamp1_tmp := DBMS_UTILITY.Get_Time;
1796:
1797: IF FII_AP_Due_Counts_B_MS.Count > 0 THEN
1798: FORALL i IN FII_AP_Due_Counts_B_MS.First..FII_AP_Due_Counts_B_MS.Last
1799: INSERT INTO FII_AP_DUE_COUNTS_B VALUES FII_AP_Due_Counts_B_MS(i);
1800: END IF;
1801:
1794: g_state := 'Bulk inserting into FII_AP_Due_Counts_B from FII_AP_Due_Counts_B_MS.';
1795: l_timestamp1_tmp := DBMS_UTILITY.Get_Time;
1796:
1797: IF FII_AP_Due_Counts_B_MS.Count > 0 THEN
1798: FORALL i IN FII_AP_Due_Counts_B_MS.First..FII_AP_Due_Counts_B_MS.Last
1799: INSERT INTO FII_AP_DUE_COUNTS_B VALUES FII_AP_Due_Counts_B_MS(i);
1800: END IF;
1801:
1802: l_timestamp1_tmp := DBMS_UTILITY.Get_Time - l_timestamp1_tmp;
1795: l_timestamp1_tmp := DBMS_UTILITY.Get_Time;
1796:
1797: IF FII_AP_Due_Counts_B_MS.Count > 0 THEN
1798: FORALL i IN FII_AP_Due_Counts_B_MS.First..FII_AP_Due_Counts_B_MS.Last
1799: INSERT INTO FII_AP_DUE_COUNTS_B VALUES FII_AP_Due_Counts_B_MS(i);
1800: END IF;
1801:
1802: l_timestamp1_tmp := DBMS_UTILITY.Get_Time - l_timestamp1_tmp;
1803:
1801:
1802: l_timestamp1_tmp := DBMS_UTILITY.Get_Time - l_timestamp1_tmp;
1803:
1804: if g_debug_flag = 'Y' then
1805: FII_UTIL.put_line('The time taken to bulk insert into FII_AP_Due_Counts_B is: ' || to_char(l_timestamp1_tmp/100) || ' seconds.');
1806: end if;
1807:
1808: l_timestamp1 := DBMS_UTILITY.Get_Time - l_timestamp1;
1809: if g_debug_flag = 'Y' then
2138: -- Procedure POPULATE_INV_PAY_SCHED_SUM
2139: -- Purpose
2140: -- This POPULATE_INV_PAY_SCHED_SUM routine inserts records into base
2141: -- summary tables FII_AP_INVOICE_B, FII_AP_PAY_SCHED_B,
2142: -- FII_AP_AGING_BKTS_B and FII_AP_DUE_COUNTS_B using data cached in memory.
2143: ------------------------------------------------------------------
2144:
2145: PROCEDURE POPULATE_INV_PAY_SCHED_SUM IS
2146: l_invoice Inv_Rec;
2152: l_timestamp2 BINARY_INTEGER := 0; --Used to keep track of total time to populate the memory structures for FII_AP_Pay_Sched_B.
2153: l_timestamp2_tmp BINARY_INTEGER;
2154: l_timestamp3 BINARY_INTEGER := 0; --Used to keep track of total time to populate the memory structures for FII_AP_Invoice_B.
2155: l_timestamp3_tmp BINARY_INTEGER;
2156: l_timestamp4 BINARY_INTEGER := 0; --Used to keep track of total time to populate FII_AP_Aging_Bkts_B and FII_AP_Due_Counts_B.
2157: l_timestamp4_tmp BINARY_INTEGER;
2158: BEGIN
2159:
2160: g_state := 'Deleting existing records from FII_AP_AGING_BKTS_B';
2167: WHERE Invoice_ID IN (SELECT Invoice_ID
2168: FROM FII_AP_Invoice_IDS
2169: WHERE Pay_Sched_B_Flag = 'Y');
2170:
2171: g_state := 'Deleting existing records from FII_AP_DUE_COUNTS_B';
2172: if g_debug_flag = 'Y' then
2173: FII_UTIL.put_line('');
2174: FII_UTIL.put_line(g_state);
2175: end if;
2173: FII_UTIL.put_line('');
2174: FII_UTIL.put_line(g_state);
2175: end if;
2176:
2177: DELETE /*+ index(A, FII_AP_DUE_COUNTS_B_N1) */ FROM FII_AP_Due_Counts_B A
2178: WHERE Invoice_ID IN (SELECT Invoice_ID
2179: FROM FII_AP_Invoice_IDS
2180: WHERE Pay_Sched_B_Flag = 'Y');
2181:
2235:
2236: l_ps_aging PS_Aging_Rec;
2237: l_ps_aging_marker VARCHAR2(50);
2238: l_aging_bkts_b FII_AP_AGING_BKTS_B%ROWTYPE;
2239: l_due_counts_b FII_AP_DUE_COUNTS_B%ROWTYPE;
2240:
2241: l_inv_db1 NUMBER := 0;
2242: l_inv_db2 NUMBER := 0;
2243: l_inv_db3 NUMBER := 0;
2784: l_due_counts_b.Last_Updated_By := g_fii_user_id;
2785: l_due_counts_b.Creation_Date := sysdate;
2786: l_due_counts_b.Created_By := g_fii_user_id;
2787: l_due_counts_b.Last_Update_Login := g_fii_login_id;
2788: FII_AP_Due_Counts_B_MS(FII_AP_Due_Counts_B_MS.Count+1) := l_due_counts_b;
2789:
2790: ELSE
2791: BEGIN
2792: l_ps_aging := FII_AP_PS_Aging_MS(to_char(l_pay_sched_b.Action_Date, 'RRRR/MM/DD'));
2996: l_due_counts_b.Last_Updated_By := g_fii_user_id;
2997: l_due_counts_b.Creation_Date := sysdate;
2998: l_due_counts_b.Created_By := g_fii_user_id;
2999: l_due_counts_b.Last_Update_Login := g_fii_login_id;
3000: FII_AP_Due_Counts_B_MS(FII_AP_Due_Counts_B_MS.Count+1) := l_due_counts_b;
3001:
3002:
3003: ELSE
3004: BEGIN
3332: l_due_counts_b.Last_Updated_By := g_fii_user_id;
3333: l_due_counts_b.Creation_Date := sysdate;
3334: l_due_counts_b.Created_By := g_fii_user_id;
3335: l_due_counts_b.Last_Update_Login := g_fii_login_id;
3336: FII_AP_Due_Counts_B_MS(FII_AP_Due_Counts_B_MS.Count+1) := l_due_counts_b;
3337: END IF;
3338:
3339: ELSE
3340: BEGIN
3582: l_due_counts_b.Last_Updated_By := g_fii_user_id;
3583: l_due_counts_b.Creation_Date := sysdate;
3584: l_due_counts_b.Created_By := g_fii_user_id;
3585: l_due_counts_b.Last_Update_Login := g_fii_login_id;
3586: FII_AP_Due_Counts_B_MS(FII_AP_Due_Counts_B_MS.Count+1) := l_due_counts_b;
3587:
3588: ELSE
3589: BEGIN
3590: l_ps_aging := FII_AP_PS_Aging_MS(to_char(l_pay_sched_b.Action_Date, 'RRRR/MM/DD'));
3835: l_due_counts_b.Last_Updated_By := g_fii_user_id;
3836: l_due_counts_b.Creation_Date := sysdate;
3837: l_due_counts_b.Created_By := g_fii_user_id;
3838: l_due_counts_b.Last_Update_Login := g_fii_login_id;
3839: FII_AP_Due_Counts_B_MS(FII_AP_Due_Counts_B_MS.Count+1) := l_due_counts_b;
3840:
3841: ELSE
3842: BEGIN
3843: l_ps_aging := FII_AP_PS_Aging_MS(to_char(l_pay_sched_b.Action_Date, 'RRRR/MM/DD'));
3993:
3994: END IF; --IF l_invoice.Invoice_B_Flag = 'Y' THEN
3995: ---------------- END INSERTING INTO FII_AP_INVOICE_B Memory Structures --------------
3996:
3997: ----------------- BEGIN INSERTING INTO FII_AP_AGING_BKTS_B_MS AND FII_AP_DUE_COUNTS_B ------------------
3998:
3999: l_timestamp4_tmp := DBMS_UTILITY.Get_Time;
4000:
4001: IF (l_invoice.Pay_Sched_B_Flag = 'Y' OR l_supplier_merge_flag = 'Y')
4000:
4001: IF (l_invoice.Pay_Sched_B_Flag = 'Y' OR l_supplier_merge_flag = 'Y')
4002: AND l_inv_has_mult_ps = 'Y' THEN
4003:
4004: g_state := 'Inserting aging records into FII_AP_Aging_Bkts_B_MS and FII_AP_Due_Counts_B_MS for Invoice ' || l_invoice.Invoice_ID || ' with multiple payment schedules.';
4005:
4006: l_ps_aging_marker := FII_AP_PS_Aging_MS.First;
4007: WHILE l_ps_aging_marker IS NOT NULL LOOP
4008: g_state := 'Looping FII_AP_PS_Aging_MS for Invoice ' || l_invoice.Invoice_ID || ', Marker ' || l_ps_aging_marker || '.';
4110: l_due_counts_b.Past_Due_Cnt := -1;
4111: END IF;
4112:
4113: IF l_due_counts_b.Due_Cnt <> 0 OR
4114: l_due_counts_b.Past_Due_Cnt <> 0 THEN --Insert into FII_AP_DUE_COUNTS_B_MS.
4115: g_state := 'Inserting aging record in FII_AP_Due_Counts_B_MS for Invoice ' || l_invoice.Invoice_ID || ', Marker ' || l_ps_aging_marker || '.';
4116:
4117: l_due_counts_b.Time_ID := TO_NUMBER(TO_CHAR(l_ps_aging.Action_Date,'J'));
4118: l_due_counts_b.Period_Type_ID := 1;
4111: END IF;
4112:
4113: IF l_due_counts_b.Due_Cnt <> 0 OR
4114: l_due_counts_b.Past_Due_Cnt <> 0 THEN --Insert into FII_AP_DUE_COUNTS_B_MS.
4115: g_state := 'Inserting aging record in FII_AP_Due_Counts_B_MS for Invoice ' || l_invoice.Invoice_ID || ', Marker ' || l_ps_aging_marker || '.';
4116:
4117: l_due_counts_b.Time_ID := TO_NUMBER(TO_CHAR(l_ps_aging.Action_Date,'J'));
4118: l_due_counts_b.Period_Type_ID := 1;
4119: l_due_counts_b.Org_ID := l_invoice.Org_ID;
4124: l_due_counts_b.Last_Updated_By := g_fii_user_id;
4125: l_due_counts_b.Creation_Date := sysdate;
4126: l_due_counts_b.Created_By := g_fii_user_id;
4127: l_due_counts_b.Last_Update_Login := g_fii_login_id;
4128: FII_AP_Due_Counts_B_MS(FII_AP_Due_Counts_B_MS.Count+1) := l_due_counts_b;
4129: END IF;
4130:
4131: l_ps_aging_marker := FII_AP_PS_Aging_MS.Next(l_ps_aging_marker);
4132: END LOOP; --End of FII_AP_PS_Aging_MS Loop.
4133: END IF; --IF l_invoice.Pay_Sched_B_Flag = 'Y' THEN
4134:
4135: l_timestamp4_tmp := DBMS_UTILITY.Get_Time - l_timestamp4_tmp;
4136: l_timestamp4 := l_timestamp4 + l_timestamp4_tmp;
4137: ------------------ END INSERTING INTO FII_AP_AGING_BKTS_B_MS AND FII_AP_DUE_COUNTS_B -------------------
4138:
4139: END;
4140: END LOOP; --End of Invoices Loop.
4141:
4145: --Print out time breakdown for each table.
4146: if g_debug_flag = 'Y' then
4147: FII_UTIL.put_line('The time taken to populate the memory structures for FII_AP_Pay_Sched_B is: ' || to_char(l_timestamp2/100) || ' seconds.');
4148: FII_UTIL.put_line('The time taken to populate the memory structures for FII_AP_Invoice_B is: ' || to_char(l_timestamp3/100) || ' seconds.');
4149: FII_UTIL.put_line('The time taken to populate FII_AP_Aging_Bkts_B_MS and FII_AP_Due_Counts_B_MS is: ' || to_char(l_timestamp4/100) || ' seconds.');
4150: end if;
4151:
4152:
4153: --Populate Tables from Memory Structures.
4169: FROM FII_AP_DBI_LOG_T
4170: WHERE Table_Name = 'AP_INVOICES'
4171: AND Operation_Flag = 'U');
4172:
4173: g_state := 'Updating Supplier_ID in FII_AP_DUE_COUNTS_B';
4174: UPDATE FII_AP_DUE_COUNTS_B DC
4175: SET Supplier_ID = (SELECT AI.Supplier_ID
4176: FROM FII_AP_Invoice_B AI
4177: WHERE AI.Invoice_ID = DC.Invoice_ID)
4170: WHERE Table_Name = 'AP_INVOICES'
4171: AND Operation_Flag = 'U');
4172:
4173: g_state := 'Updating Supplier_ID in FII_AP_DUE_COUNTS_B';
4174: UPDATE FII_AP_DUE_COUNTS_B DC
4175: SET Supplier_ID = (SELECT AI.Supplier_ID
4176: FROM FII_AP_Invoice_B AI
4177: WHERE AI.Invoice_ID = DC.Invoice_ID)
4178: WHERE DC.Invoice_ID IN (SELECT Key_Value1_Num