[Home] [Help]
181: --Invoice_B_Type is used to populate the memory structure FII_AP_Invoice_B_MS
182: TYPE Invoice_B_Type IS TABLE OF FII_AP_INVOICE_B%ROWTYPE
183: INDEX BY BINARY_INTEGER;
184:
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
182: TYPE Invoice_B_Type IS TABLE OF FII_AP_INVOICE_B%ROWTYPE
183: INDEX BY BINARY_INTEGER;
184:
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
220: FII_AP_Pay_Sched_MS Pay_Sched_Type;
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;
645: FII_UTIL.put_line('');
646: FII_UTIL.put_line(g_state);
647: end if;
648:
649: g_state := 'Deleting records from the FII_AP_AGING_BKTS_B table';
650: if g_debug_flag = 'Y' then
651: FII_UTIL.put_line('');
652: FII_UTIL.put_line(g_state);
653: end if;
651: FII_UTIL.put_line('');
652: FII_UTIL.put_line(g_state);
653: end if;
654:
655: DELETE FROM FII_AP_AGING_BKTS_B
656: WHERE Invoice_ID IN (SELECT Key_Value1_Num
657: FROM FII_AP_DBI_LOG_T
658: WHERE Table_Name = 'AP_INVOICES'
659: AND Operation_Flag = 'D');
658: WHERE Table_Name = 'AP_INVOICES'
659: AND Operation_Flag = 'D');
660:
661: if g_debug_flag = 'Y' then
662: FII_UTIL.put_line('Deleted '|| SQL%ROWCOUNT ||' records from the FII_AP_AGING_BKTS_B');
663: FII_UTIL.put_line('');
664: end if;
665:
666:
1776: FII_UTIL.put_line('The time taken to bulk insert records into FII_AP_Pay_Sched_UI_GT is: ' || to_char(l_timestamp1_tmp/100) || ' seconds.');
1777: end if;
1778:
1779:
1780: g_state := 'Bulk inserting into FII_AP_Aging_Bkts_B from FII_AP_Aging_Bkts_B_MS.';
1781: l_timestamp1_tmp := DBMS_UTILITY.Get_Time;
1782:
1783: IF FII_AP_Aging_Bkts_B_MS.Count > 0 THEN
1784: FORALL i IN FII_AP_Aging_Bkts_B_MS.First..FII_AP_Aging_Bkts_B_MS.Last
1779:
1780: g_state := 'Bulk inserting into FII_AP_Aging_Bkts_B from FII_AP_Aging_Bkts_B_MS.';
1781: l_timestamp1_tmp := DBMS_UTILITY.Get_Time;
1782:
1783: IF FII_AP_Aging_Bkts_B_MS.Count > 0 THEN
1784: FORALL i IN FII_AP_Aging_Bkts_B_MS.First..FII_AP_Aging_Bkts_B_MS.Last
1785: INSERT INTO FII_AP_AGING_BKTS_B VALUES FII_AP_Aging_Bkts_B_MS(i);
1786: END IF;
1787:
1780: g_state := 'Bulk inserting into FII_AP_Aging_Bkts_B from FII_AP_Aging_Bkts_B_MS.';
1781: l_timestamp1_tmp := DBMS_UTILITY.Get_Time;
1782:
1783: IF FII_AP_Aging_Bkts_B_MS.Count > 0 THEN
1784: FORALL i IN FII_AP_Aging_Bkts_B_MS.First..FII_AP_Aging_Bkts_B_MS.Last
1785: INSERT INTO FII_AP_AGING_BKTS_B VALUES FII_AP_Aging_Bkts_B_MS(i);
1786: END IF;
1787:
1788: l_timestamp1_tmp := DBMS_UTILITY.Get_Time - l_timestamp1_tmp;
1781: l_timestamp1_tmp := DBMS_UTILITY.Get_Time;
1782:
1783: IF FII_AP_Aging_Bkts_B_MS.Count > 0 THEN
1784: FORALL i IN FII_AP_Aging_Bkts_B_MS.First..FII_AP_Aging_Bkts_B_MS.Last
1785: INSERT INTO FII_AP_AGING_BKTS_B VALUES FII_AP_Aging_Bkts_B_MS(i);
1786: END IF;
1787:
1788: l_timestamp1_tmp := DBMS_UTILITY.Get_Time - l_timestamp1_tmp;
1789:
1787:
1788: l_timestamp1_tmp := DBMS_UTILITY.Get_Time - l_timestamp1_tmp;
1789:
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;
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';
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';
2161: if g_debug_flag = 'Y' then
2162: FII_UTIL.put_line('');
2163: FII_UTIL.put_line(g_state);
2164: end if;
2162: FII_UTIL.put_line('');
2163: FII_UTIL.put_line(g_state);
2164: end if;
2165:
2166: DELETE /*+ index(A, FII_AP_AGING_BKTS_B_N1) */ FROM FII_AP_Aging_Bkts_B A
2167: WHERE Invoice_ID IN (SELECT Invoice_ID
2168: FROM FII_AP_Invoice_IDS
2169: WHERE Pay_Sched_B_Flag = 'Y');
2170:
2234: l_invoice_b FII_AP_INVOICE_B%ROWTYPE;
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;
2769: l_aging_bkts_b.Last_Updated_By := g_fii_user_id;
2770: l_aging_bkts_b.Creation_Date := sysdate;
2771: l_aging_bkts_b.Created_By := g_fii_user_id;
2772: l_aging_bkts_b.Last_Update_Login := g_fii_login_id;
2773: FII_AP_Aging_Bkts_B_MS(FII_AP_Aging_Bkts_B_MS.Count+1) := l_aging_bkts_b;
2774:
2775: l_due_counts_b.Time_ID := TO_NUMBER(TO_CHAR(l_pay_sched_b.Action_Date,'J'));
2776: l_due_counts_b.Period_Type_ID := 1;
2777: l_due_counts_b.Org_ID := l_invoice.Org_ID;
2981: l_aging_bkts_b.Last_Updated_By := g_fii_user_id;
2982: l_aging_bkts_b.Creation_Date := sysdate;
2983: l_aging_bkts_b.Created_By := g_fii_user_id;
2984: l_aging_bkts_b.Last_Update_Login := g_fii_login_id;
2985: FII_AP_Aging_Bkts_B_MS(FII_AP_Aging_Bkts_B_MS.Count+1) := l_aging_bkts_b;
2986:
2987: l_due_counts_b.Time_ID := TO_NUMBER(TO_CHAR(l_pay_sched_b.Action_Date,'J'));
2988: l_due_counts_b.Period_Type_ID := 1;
2989: l_due_counts_b.Org_ID := l_invoice.Org_ID;
3315: l_aging_bkts_b.Last_Updated_By := g_fii_user_id;
3316: l_aging_bkts_b.Creation_Date := sysdate;
3317: l_aging_bkts_b.Created_By := g_fii_user_id;
3318: l_aging_bkts_b.Last_Update_Login := g_fii_login_id;
3319: FII_AP_Aging_Bkts_B_MS(FII_AP_Aging_Bkts_B_MS.Count+1) := l_aging_bkts_b;
3320:
3321: IF l_ps_db1 + l_ps_db2 + l_ps_db3 <> 0 OR
3322: l_ps_pdb1 + l_ps_pdb2 + l_ps_pdb3 <> 0 THEN
3323: l_due_counts_b.Time_ID := TO_NUMBER(TO_CHAR(l_pay_sched_b.Action_Date,'J'));
3567: l_aging_bkts_b.Last_Updated_By := g_fii_user_id;
3568: l_aging_bkts_b.Creation_Date := sysdate;
3569: l_aging_bkts_b.Created_By := g_fii_user_id;
3570: l_aging_bkts_b.Last_Update_Login := g_fii_login_id;
3571: FII_AP_Aging_Bkts_B_MS(FII_AP_Aging_Bkts_B_MS.Count+1) := l_aging_bkts_b;
3572:
3573: l_due_counts_b.Time_ID := TO_NUMBER(TO_CHAR(l_pay_sched_b.Action_Date,'J'));
3574: l_due_counts_b.Period_Type_ID := 1;
3575: l_due_counts_b.Org_ID := l_invoice.Org_ID;
3820: l_aging_bkts_b.Last_Updated_By := g_fii_user_id;
3821: l_aging_bkts_b.Creation_Date := sysdate;
3822: l_aging_bkts_b.Created_By := g_fii_user_id;
3823: l_aging_bkts_b.Last_Update_Login := g_fii_login_id;
3824: FII_AP_Aging_Bkts_B_MS(FII_AP_Aging_Bkts_B_MS.Count+1) := l_aging_bkts_b;
3825:
3826: l_due_counts_b.Time_ID := TO_NUMBER(TO_CHAR(l_pay_sched_b.Action_Date,'J'));
3827: l_due_counts_b.Period_Type_ID := 1;
3828: l_due_counts_b.Org_ID := l_invoice.Org_ID;
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 || '.';
4067: l_aging_bkts_b.Due_Bucket2_Cnt <> 0 OR
4068: l_aging_bkts_b.Due_Bucket3_Cnt <> 0 OR
4069: l_aging_bkts_b.Past_Due_Bucket3_Cnt <> 0 OR
4070: l_aging_bkts_b.Past_Due_Bucket2_Cnt <> 0 OR
4071: l_aging_bkts_b.Past_Due_Bucket1_Cnt <> 0 THEN --Insert into FII_AP_AGING_BKTS_B_MS.
4072:
4073: g_state := 'Inserting aging record in FII_AP_Aging_Bkts_B_MS for Invoice ' || l_invoice.Invoice_ID || ', Marker ' || l_ps_aging_marker || '.';
4074:
4075: l_aging_bkts_b.Time_ID := TO_NUMBER(TO_CHAR(l_ps_aging.Action_Date,'J'));
4069: l_aging_bkts_b.Past_Due_Bucket3_Cnt <> 0 OR
4070: l_aging_bkts_b.Past_Due_Bucket2_Cnt <> 0 OR
4071: l_aging_bkts_b.Past_Due_Bucket1_Cnt <> 0 THEN --Insert into FII_AP_AGING_BKTS_B_MS.
4072:
4073: g_state := 'Inserting aging record in FII_AP_Aging_Bkts_B_MS for Invoice ' || l_invoice.Invoice_ID || ', Marker ' || l_ps_aging_marker || '.';
4074:
4075: l_aging_bkts_b.Time_ID := TO_NUMBER(TO_CHAR(l_ps_aging.Action_Date,'J'));
4076: l_aging_bkts_b.Period_Type_ID := 1;
4077: l_aging_bkts_b.Org_ID := l_invoice.Org_ID;
4082: l_aging_bkts_b.Last_Updated_By := g_fii_user_id;
4083: l_aging_bkts_b.Creation_Date := sysdate;
4084: l_aging_bkts_b.Created_By := g_fii_user_id;
4085: l_aging_bkts_b.Last_Update_Login := g_fii_login_id;
4086: FII_AP_Aging_Bkts_B_MS(FII_AP_Aging_Bkts_B_MS.Count+1) := l_aging_bkts_b;
4087: END IF;
4088:
4089: IF l_inv_due = 0 AND l_ps_aging.Due_Bucket1 + l_ps_aging.Due_Bucket2 + l_ps_aging.Due_Bucket3 > 0 THEN
4090: l_due_counts_b.Due_Cnt := 1;
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.
4159: --Update, Insert, or Delete Records into FII_AP_Invoice_B.
4160: MAINTAIN_INVOICE_B;
4161:
4162:
4163: g_state := 'Updating Supplier_ID in FII_AP_AGING_BKTS_B';
4164: UPDATE FII_AP_AGING_BKTS_B AB
4165: SET Supplier_ID = (SELECT AI.Supplier_ID
4166: FROM FII_AP_Invoice_B AI
4167: WHERE AI.Invoice_ID = AB.Invoice_ID)
4160: MAINTAIN_INVOICE_B;
4161:
4162:
4163: g_state := 'Updating Supplier_ID in FII_AP_AGING_BKTS_B';
4164: UPDATE FII_AP_AGING_BKTS_B AB
4165: SET Supplier_ID = (SELECT AI.Supplier_ID
4166: FROM FII_AP_Invoice_B AI
4167: WHERE AI.Invoice_ID = AB.Invoice_ID)
4168: WHERE AB.Invoice_ID IN (SELECT Key_Value1_Num