[Home] [Help]
297: --End PA-K Changes
298:
299: --Bug 2905892
300: G_FC_Gl_Date DATE;
301: G_FC_Period_Name pa_bc_packets.period_name%Type;
302: G_FC_Period_Year pa_bc_packets.period_year%type; --REL12
303: G_PrevFCGLDate DATE;
304: G_PrevFCPdName pa_bc_packets.period_name%Type;
305:
298:
299: --Bug 2905892
300: G_FC_Gl_Date DATE;
301: G_FC_Period_Name pa_bc_packets.period_name%Type;
302: G_FC_Period_Year pa_bc_packets.period_year%type; --REL12
303: G_PrevFCGLDate DATE;
304: G_PrevFCPdName pa_bc_packets.period_name%Type;
305:
306: PROCEDURE tr_import_funds_check (p_pa_date IN DATE,
300: G_FC_Gl_Date DATE;
301: G_FC_Period_Name pa_bc_packets.period_name%Type;
302: G_FC_Period_Year pa_bc_packets.period_year%type; --REL12
303: G_PrevFCGLDate DATE;
304: G_PrevFCPdName pa_bc_packets.period_name%Type;
305:
306: PROCEDURE tr_import_funds_check (p_pa_date IN DATE,
307: p_txn_source IN VARCHAR2,
308: p_acct_raw_cost IN NUMBER,
1662:
1663: Cursor c_check_fail(c_packet_id NUMBER) is
1664: Select 'X'
1665: from Dual
1666: Where exists (select 'Y' from pa_bc_packets
1667: Where packet_id = c_packet_id
1668: and substr(nvl(result_code, 'P'),1,1) = 'F');
1669:
1670:
5708: log_message('log_message: Value of p_fc_document_type is= ' || p_fc_document_type);
5709: END IF;
5710:
5711: --Bug 2905892
5712: --Assign txn gl_Date for insert of the reversing and EXP lines into pa_bc_packets
5713: G_FC_Gl_Date := X_Gl_Date;
5714:
5715: tr_import_funds_check (p_pa_date => pa_date,
5716: p_txn_source => X_trx_src,
10363: b.cdl_system_reference4,
10364: a.project_id,
10365: a.budget_line_id ,
10366: a.budget_version_id
10367: from pa_bc_packets a,
10368: pa_transaction_interface b
10369: where a.txn_interface_id = b.txn_interface_id
10370: and a.document_header_id = b.cdl_system_reference2
10371: and a.document_distribution_id = decode(p_calling_mode, 'AP VARIANCE',b.cdl_system_reference5
10388: b.cdl_system_reference4,
10389: a.project_id,
10390: a.budget_line_id ,
10391: a.budget_version_id
10392: from pa_bc_packets a,
10393: pa_transaction_interface b,-- moac changes
10394: pa_expenditure_items c
10395: where a.txn_interface_id = b.txn_interface_id
10396: and a.document_header_id = b.expenditure_item_id
10415: b.cdl_system_reference4,
10416: a.project_id,
10417: a.budget_line_id ,
10418: a.budget_version_id
10419: from pa_bc_packets a,
10420: pa_transaction_interface b -- moac changes
10421: where a.txn_interface_id = b.txn_interface_id
10422: and a.document_header_id = b.cdl_system_reference2
10423: and a.document_distribution_id = b.cdl_system_reference5
10435: b.cdl_system_reference4,
10436: a.project_id,
10437: a.budget_line_id ,
10438: a.budget_version_id
10439: from pa_bc_packets a,
10440: pa_transaction_interface b, -- moac changes
10441: ap_invoice_distributions c,
10442: po_distributions d
10443: where a.txn_interface_id = b.txn_interface_id
10460: b.cdl_system_reference4,
10461: a.project_id,
10462: a.budget_line_id ,
10463: a.budget_version_id
10464: from pa_bc_packets a,
10465: pa_transaction_interface b,
10466: ap_self_assessed_tax_dist c,
10467: po_distributions d
10468: where a.txn_interface_id = b.txn_interface_id
10481: CURSOR c_exp_item_id IS
10482: SELECT txn.expenditure_item_id ,
10483: pkt.bc_packet_id
10484: FROM pa_transaction_interface txn,-- moac changes
10485: pa_bc_packets pkt
10486: WHERE pkt.txn_interface_id = txn.txn_interface_id
10487: and pkt.request_id = g_request_id
10488: and txn.transaction_status_code = 'A'
10489: and pkt.status_code = 'P'
10494: l_packet_id NUMBER ;
10495: l_sys_ref2 NUMBER ;
10496: l_sys_ref3 NUMBER ;
10497: l_sys_ref4 VARCHAR2(30) ;
10498: l_doc_dist_id pa_bc_packets.document_distribution_id%TYPE ;
10499: l_ExpItemId_temp_Tab PA_PLSQL_DATATYPES.Num15TabTyp;
10500: l_budget_ccid NUMBER ;
10501: l_old_pkt NUMBER := 0;
10502: l_project_id NUMBER ;
10499: l_ExpItemId_temp_Tab PA_PLSQL_DATATYPES.Num15TabTyp;
10500: l_budget_ccid NUMBER ;
10501: l_old_pkt NUMBER := 0;
10502: l_project_id NUMBER ;
10503: l_bc_pkt_id pa_bc_packets.bc_packet_id%TYPE;
10504: l_exp_item_id pa_expenditure_items_all.expenditure_item_id%TYPE;
10505: l_budget_line_id pa_bc_packets.budget_line_id%TYPE;
10506: l_budget_version_id pa_bc_packets.budget_version_id%TYPE;
10507:
10501: l_old_pkt NUMBER := 0;
10502: l_project_id NUMBER ;
10503: l_bc_pkt_id pa_bc_packets.bc_packet_id%TYPE;
10504: l_exp_item_id pa_expenditure_items_all.expenditure_item_id%TYPE;
10505: l_budget_line_id pa_bc_packets.budget_line_id%TYPE;
10506: l_budget_version_id pa_bc_packets.budget_version_id%TYPE;
10507:
10508:
10509: PROCEDURE Upd_Sts_Enc_Bal(p_packet_id in number,
10502: l_project_id NUMBER ;
10503: l_bc_pkt_id pa_bc_packets.bc_packet_id%TYPE;
10504: l_exp_item_id pa_expenditure_items_all.expenditure_item_id%TYPE;
10505: l_budget_line_id pa_bc_packets.budget_line_id%TYPE;
10506: l_budget_version_id pa_bc_packets.budget_version_id%TYPE;
10507:
10508:
10509: PROCEDURE Upd_Sts_Enc_Bal(p_packet_id in number,
10510: x_return_status out NOCOPY varchar2 ) is
10523:
10524: -- R12 funds management : 'C' status code has been obsoleted
10525: l_records_updated := 0;
10526:
10527: -- Bug 5372480 : Records of pa_bc_packets are marked to 'A' status before calling pa_funds_control_pkg.UPD_BDGT_ENCUM_BAL
10528: -- because the CURSOR bdgt_encum_details in pa_funds_control_pkg.UPD_BDGT_ENCUM_BAL queries for 'A' status records from pa_bc_packets.
10529: -- Also there is no need to loop for distinct projects earlier being fetched by cursor c_bcpkt_projects.
10530:
10531: update pa_bc_packets a
10524: -- R12 funds management : 'C' status code has been obsoleted
10525: l_records_updated := 0;
10526:
10527: -- Bug 5372480 : Records of pa_bc_packets are marked to 'A' status before calling pa_funds_control_pkg.UPD_BDGT_ENCUM_BAL
10528: -- because the CURSOR bdgt_encum_details in pa_funds_control_pkg.UPD_BDGT_ENCUM_BAL queries for 'A' status records from pa_bc_packets.
10529: -- Also there is no need to loop for distinct projects earlier being fetched by cursor c_bcpkt_projects.
10530:
10531: update pa_bc_packets a
10532: set a.status_code = 'A'
10527: -- Bug 5372480 : Records of pa_bc_packets are marked to 'A' status before calling pa_funds_control_pkg.UPD_BDGT_ENCUM_BAL
10528: -- because the CURSOR bdgt_encum_details in pa_funds_control_pkg.UPD_BDGT_ENCUM_BAL queries for 'A' status records from pa_bc_packets.
10529: -- Also there is no need to loop for distinct projects earlier being fetched by cursor c_bcpkt_projects.
10530:
10531: update pa_bc_packets a
10532: set a.status_code = 'A'
10533: where a.packet_id = p_packet_id
10534: and a.request_id = g_request_id
10535: and a.status_code = 'P';
10599: END IF;
10600: exit;
10601: end if;
10602:
10603: UPDATE pa_bc_packets
10604: SET document_header_id = l_exp_item_id,
10605: reference1 = l_exp_item_id
10606: WHERE bc_packet_id = l_bc_pkt_id;
10607: END LOOP;
10894: close select_disc_packets;
10895: end if;
10896:
10897: /* Start of bug 3239837 */
10898: pa_debug.G_err_stage := 'Update rejected interface records in pa_bc_packets to T';
10899: pa_cc_utils.log_message(pa_debug.G_err_Stage);
10900:
10901: update pa_bc_packets
10902: set status_code = 'T',
10897: /* Start of bug 3239837 */
10898: pa_debug.G_err_stage := 'Update rejected interface records in pa_bc_packets to T';
10899: pa_cc_utils.log_message(pa_debug.G_err_Stage);
10900:
10901: update pa_bc_packets
10902: set status_code = 'T',
10903: result_code = 'F140'
10904: where request_id = g_request_id
10905: and status_code = 'P'
10913:
10914: pa_debug.G_err_stage := 'In stage 1 Updated count to T = '|| SQL%ROWCOUNT;
10915: pa_cc_utils.log_message(pa_debug.G_err_Stage);
10916:
10917: update pa_bc_packets
10918: set status_code = 'T',
10919: result_code = 'F140'
10920: where request_id = g_request_id
10921: and status_code = 'P'
10945: IF PG_DEBUG = 'Y' THEN
10946: log_message('log_message: Before update to T');
10947: END IF;
10948:
10949: update pa_bc_packets
10950: set status_code = 'T',
10951: result_code = 'F140'
10952: where status_code = 'P'
10953: and request_id = G_REQUEST_ID;
11013: --R12 Funds Management Uptake : Deleted variables defined for storing encumbrance types
11014: l_project_id NUMBER(15);
11015: l_base_qv NUMBER;
11016: l_base_av NUMBER;
11017: l_period_year pa_bc_packets.period_year%type; --REL12
11018: l_inv_type ap_invoices_all.invoice_type_lookup_code%TYPE;
11019: l_line_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%TYPE;
11020:
11021: -- R12 AP lines uptake : Prepayment changes :Cursor to fetch AP related details.
11050: --Derive Period_Name for the given GL_Date from GL_Period_Statuses
11051: --for Application_Id 101.
11052: --Funds checking is done using GL periods (application id = 101)
11053: FUNCTION Get_FC_Period_Name(P_GL_Date IN DATE) RETURN VARCHAR2 IS
11054: l_Period_Name pa_bc_packets.period_name%type;
11055: BEGIN
11056:
11057: IF PG_DEBUG = 'Y' THEN
11058: pa_debug.G_err_stage := 'Inside Get_FC_Period_Name function';
11101: END IF;
11102:
11103: --
11104: -- 1. Generate a packet identifier for all the records that
11105: -- needs to be inserted into Pa BC Packets
11106: --
11107: IF PG_DEBUG = 'Y' THEN
11108: pa_debug.G_err_stage := 'Generateing new packet Id';
11109: log_message('log_message: ' || pa_debug.G_err_Stage);
11117: rather than in the beginning
11118: */
11119:
11120: --Bug 2905892
11121: --Get the GL Period Name from txn's gl_date. This will be inserted into pa_bc_packets
11122: --for both the reversing raw and burden lines and also the EXP lines.
11123: --Funds Check API will derive the GL_Date based on the Period_Name, so what
11124: --we insert for gl_date is irrelevant here.
11125: IF PG_DEBUG = 'Y' THEN
11650: /* Bug 5731450 : The following two select statements are modified to also fetch the PO exchange rate. */
11651:
11652: select sum(nvl(accounted_dr,0)-nvl(accounted_cr,0)),SUM(POD.rate)/SUM(1)
11653: into l_pkt_summ,l_pkt_rate
11654: from pa_bc_packets bcc,
11655: po_distributions pod --Added for Bug#3693974
11656: where pod.po_header_id = p_sys_ref2
11657: and pod.po_distribution_id = p_sys_ref3
11658: and ((
11979: IF PG_DEBUG = 'Y' THEN
11980: log_message('log_message: ' || 'Inside Upd_PktSts_Fatal');
11981: END IF;
11982:
11983: update pa_bc_packets
11984: set status_code = 'T',
11985: result_code = 'F140'
11986: where status_code = 'P'
11987: and request_id = P_REQUEST_ID;
12015: IF PG_DEBUG = 'Y' THEN
12016: log_message('log_message: ' || 'Inside Upd_PktSts,packet_id = '|| p_packet_id);
12017: END IF;
12018:
12019: update pa_bc_packets
12020: set status_code = 'T',
12021: result_code = 'F140'
12022: where status_code = 'P'
12023: --Bug 3592289 changed p_packet_id to packet_id
12592:
12593: -- Fetch BC Pkt Raw outstanding amount
12594: CURSOR C_get_raw_pkt_amt IS
12595: select sum(nvl(accounted_dr,0)-nvl(accounted_cr,0))
12596: from pa_bc_packets bcc
12597: where bcc.document_type = 'AP'
12598: and bcc.document_header_id = p_sys_ref2
12599: and bcc.document_distribution_id = p_sys_ref5
12600: and ( (bcc.Status_Code in('A','C'))
12995: END IF;
12996: RAISE;
12997: END insert_ap_bc_packets;
12998:
12999: -- This procedure inserts records into pa_bc_packets for relieving commitment raw and burden
13000: -- and also for reserving raw and burden against actual.
13001: -- 1. IF p_fc_document_type = 'CMT'/'ALL' then we need to relieve commitment amount.
13002: -- 2. If commitment exists in pa_bc_commitments (i.e. sweeper process if run) then
13003: -- insert records into pa_bc_packets to relieve the raw and burden commitment
12999: -- This procedure inserts records into pa_bc_packets for relieving commitment raw and burden
13000: -- and also for reserving raw and burden against actual.
13001: -- 1. IF p_fc_document_type = 'CMT'/'ALL' then we need to relieve commitment amount.
13002: -- 2. If commitment exists in pa_bc_commitments (i.e. sweeper process if run) then
13003: -- insert records into pa_bc_packets to relieve the raw and burden commitment
13004: -- amounts lying in pa_bc_commitments.
13005: -- else
13006: -- insert records into pa_bc_packets to relieve the raw and burden commitment
13007: -- amounts lying in pa_bc_packets.
13002: -- 2. If commitment exists in pa_bc_commitments (i.e. sweeper process if run) then
13003: -- insert records into pa_bc_packets to relieve the raw and burden commitment
13004: -- amounts lying in pa_bc_commitments.
13005: -- else
13006: -- insert records into pa_bc_packets to relieve the raw and burden commitment
13007: -- amounts lying in pa_bc_packets.
13008: -- end if;
13009: -- 3.p_document type = 'ALL' and p_adj_exp_item_id IS NULL implies its a regular
13010: -- Payable Invoice getting interfaced to Projects. In this case
13003: -- insert records into pa_bc_packets to relieve the raw and burden commitment
13004: -- amounts lying in pa_bc_commitments.
13005: -- else
13006: -- insert records into pa_bc_packets to relieve the raw and burden commitment
13007: -- amounts lying in pa_bc_packets.
13008: -- end if;
13009: -- 3.p_document type = 'ALL' and p_adj_exp_item_id IS NULL implies its a regular
13010: -- Payable Invoice getting interfaced to Projects. In this case
13011: -- insert records into pa_bc_packets to reserve actual raw cost amount which is equal to
13007: -- amounts lying in pa_bc_packets.
13008: -- end if;
13009: -- 3.p_document type = 'ALL' and p_adj_exp_item_id IS NULL implies its a regular
13010: -- Payable Invoice getting interfaced to Projects. In this case
13011: -- insert records into pa_bc_packets to reserve actual raw cost amount which is equal to
13012: -- commitment amount in pa_bc_commitments relieved in step 2.
13013: -- Note : parent_bc_packet_id is populated to -1 such that pa_funds_check process will
13014: -- generate burden against this actual based on latest compiled set id.
13015: -- 4.If p_document type = 'ACT'/'ALL' and p_adj_exp_item_id IS NOT NULL then it implies thats its a
13014: -- generate burden against this actual based on latest compiled set id.
13015: -- 4.If p_document type = 'ACT'/'ALL' and p_adj_exp_item_id IS NOT NULL then it implies thats its a
13016: -- reversing/cancelled Payable Invoice getting interfaced to Projects and original
13017: -- interfaced exp in project has been adjusted .
13018: -- In this case insert records into pa_bc_packets to fundscheck actual raw cost which is equal to
13019: -- amount in pa_transaction_interface for each non net zero expenditures associated with this
13020: -- invoice.
13021: -- Note : In this scenraio if non net zero expenditures associated with this invoice are not costed
13022: -- then import process (PAAPIMPB.pls) will reject the transactions with PA_EI_NOT_COST_DISTRIBUTED.
13041:
13042: PRAGMA AUTONOMOUS_TRANSACTION;
13043:
13044: CURSOR c_gen_raw_bc_pkt IS
13045: SELECT Pa_Bc_Packets_s.NextVal
13046: FROM dual;
13047:
13048: CURSOR c_get_po_dist_id IS
13049: SELECT po_distribution_id
13163: ,bcc.parent_bc_packet_id
13164: ,bcc.encumbrance_type_id
13165: ,bcc.document_header_id_2
13166: ,bcc.document_distribution_type
13167: From Pa_Bc_Packets bcc
13168: where bcc.Document_Header_Id = p_sys_ref2
13169: and bcc.Document_Distribution_Id =p_sys_ref5
13170: and bcc.Status_Code in ('A','C')
13171: and bcc.document_type = 'AP'
13182: (bcc.parent_bc_packet_id is NOT null AND p_txn_source = 'AP VARIANCE')
13183: );
13184:
13185: l_rec_bc_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_commitments
13186: l_rec_pkt_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_packets
13187: L_RAW_BC_PACKET_ID NUMBER;
13188: l_po_dist_id ap_invoice_distributions.po_distribution_id%TYPE;
13189: l_po_line_id po_distributions_all.po_line_id%TYPE;
13190: l_vendor_id ap_invoices_all.vendor_id%TYPE; /*bug 13601768*/
13190: l_vendor_id ap_invoices_all.vendor_id%TYPE; /*bug 13601768*/
13191:
13192: CURSOR c_curr_raw_pkt IS
13193: SELECT bc_packet_id
13194: FROM pa_bc_packets
13195: WHERE packet_id = p_packet_id
13196: AND Document_Header_Id = p_sys_ref2
13197: AND Document_Distribution_Id =p_sys_ref5
13198: AND parent_bc_packet_id IS NULL
13231: END IF;
13232:
13233: -- Code to derive values for variables which will decide the flow of this procedure
13234: l_rec_bc_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_commitments
13235: l_rec_pkt_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_packets
13236:
13237: IF p_comm_fc_req ='Y' OR p_act_fc_req = 'Y' THEN
13238:
13239: IF PG_DEBUG = 'Y' THEN
13333: IF p_comm_fc_req = 'Y' THEN
13334:
13335: -- Logic to insert commitment relieving raw and burden records
13336: FORALL i IN 1 .. g_bc_packet_id_tbl.count
13337: Insert Into Pa_Bc_Packets
13338: (Packet_Id
13339: ,Bc_Packet_Id
13340: ,Parent_Bc_Packet_Id
13341: ,Set_Of_Books_Id
13451: log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous -Old Bc Packet Id = ' || c_cur.bc_packet_id||'New Bc Packet Id = ' || l_Raw_Bc_Packet_Id ||'Packet Id = ' || p_packet_id);
13452: END IF;
13453:
13454: -- Update raw records with new bc_packet_id
13455: UPDATE Pa_Bc_Packets
13456: SET bc_packet_id = l_Raw_Bc_Packet_Id
13457: WHERE packet_id = p_packet_id
13458: AND bc_packet_id = c_cur.bc_packet_id;
13459:
13461: log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - NUmber of raw packets updated with new bc_packet_id '||SQL%ROWCOUNT);
13462: END IF;
13463:
13464: -- Update burden records with new bc_packet_id abd parent_bc_packet_id
13465: UPDATE Pa_Bc_Packets
13466: SET bc_packet_id = Pa_Bc_Packets_s.NextVal
13467: ,parent_bc_packet_id = l_Raw_Bc_Packet_Id
13468: WHERE packet_id = p_packet_id
13469: AND parent_bc_packet_id = c_cur.bc_packet_id;
13462: END IF;
13463:
13464: -- Update burden records with new bc_packet_id abd parent_bc_packet_id
13465: UPDATE Pa_Bc_Packets
13466: SET bc_packet_id = Pa_Bc_Packets_s.NextVal
13467: ,parent_bc_packet_id = l_Raw_Bc_Packet_Id
13468: WHERE packet_id = p_packet_id
13469: AND parent_bc_packet_id = c_cur.bc_packet_id;
13470:
13485:
13486: FOR i IN 1 .. g_bc_packet_id_tbl.count LOOP
13487:
13488:
13489: Insert Into Pa_Bc_Packets
13490: (Packet_Id
13491: ,Bc_Packet_Id
13492: ,Parent_Bc_Packet_Id
13493: ,Set_Of_Books_Id
13532: ,txn_interface_id --REL12
13533: ,vendor_id
13534: )
13535: Select p_Packet_Id
13536: ,Pa_Bc_Packets_s.NextVal
13537: ,-1
13538: ,g_sob_Id_tbl(i)
13539: ,G_FC_Gl_Date
13540: ,G_FC_Period_Name
13597: -- bc_commitments/bc_packets for same inv distribution, hence the global varaibles will have
13598: -- multiple distributions.But there will be only one ap discount line for both item amount +
13599: -- qty/amt varaince. Hence actuals need to reserved only once.
13600: /* Bug 5984525 : The following code is modified so that ACTUAL lines are inserted correctly
13601: into pa bc packets while interfacing discounts to Projects. */
13602: IF p_txn_source = 'AP DISCOUNTS' AND SQL%ROWCOUNT = 1 THEN
13603: IF PG_DEBUG = 'Y' THEN
13604: log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - Inserted 1 ACTUAL raw line(s) into bc packets');
13605: END IF;
13627: log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - Before inserting adjusted ACTUAL raw line from transaction import table');
13628: END IF;
13629:
13630: FORALL i IN 1 .. g_xface_Txn_interface_tbl.count
13631: Insert Into Pa_Bc_Packets
13632: (Packet_Id
13633: ,Bc_Packet_Id
13634: ,Parent_Bc_Packet_Id
13635: ,Set_Of_Books_Id
13673: ,txn_interface_id --REL12
13674: ,vendor_id
13675: )
13676: Select p_Packet_Id
13677: ,Pa_Bc_Packets_s.NextVal
13678: ,-1
13679: ,g_sobid
13680: ,G_FC_Gl_Date
13681: ,G_FC_Period_Name
13922: END IF;
13923: RAISE;
13924: END insert_cash_ap_bc_packets;
13925:
13926: -- This procedure inserts records into pa_bc_packets for relieving commitment raw and burden
13927: -- and also for reserving raw and burden against actual.
13928:
13929: -- Parameter values : p_packet_id - Packet Id to be inserted
13930: -- p_sys_ref2 - Invoice Id
13954:
13955: PRAGMA AUTONOMOUS_TRANSACTION;
13956:
13957: CURSOR c_gen_raw_bc_pkt IS
13958: SELECT Pa_Bc_Packets_s.NextVal
13959: FROM dual;
13960:
13961: CURSOR c_get_po_dist_id IS
13962: SELECT po_distribution_id
14049: ,bcc.Org_Id
14050: ,bcc.Pa_Date
14051: ,bcc.bc_packet_id
14052: ,bcc.packet_id
14053: From Pa_Bc_Packets bcc
14054: where bcc.Document_Header_Id = p_sys_ref2
14055: and bcc.Document_Distribution_Id = p_sys_ref5
14056: and bcc.Status_Code in ('A','C')
14057: and bcc.Parent_Bc_Packet_Id is NULL
14061: -- R12 Ap lines Uptake : Added for cash based accounting which can have multiple packets associated witha distribution.
14062: and rownum = 1 ;
14063:
14064: l_rec_bc_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_commitments
14065: l_rec_pkt_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_packets
14066: l_Raw_Bc_Packet_Id pa_bc_packets.bc_packet_id%TYPE;
14067: l_po_dist_id ap_invoice_distributions.po_distribution_id%TYPE;
14068: l_po_line_id po_distributions_all.po_line_id%TYPE;
14069: l_vendor_id ap_invoices_all.vendor_id%TYPE; /*bug 13601768*/
14062: and rownum = 1 ;
14063:
14064: l_rec_bc_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_commitments
14065: l_rec_pkt_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_packets
14066: l_Raw_Bc_Packet_Id pa_bc_packets.bc_packet_id%TYPE;
14067: l_po_dist_id ap_invoice_distributions.po_distribution_id%TYPE;
14068: l_po_line_id po_distributions_all.po_line_id%TYPE;
14069: l_vendor_id ap_invoices_all.vendor_id%TYPE; /*bug 13601768*/
14070:
14100:
14101: -- Code to derive values for variables which will decide the flow of this procedure
14102:
14103: l_rec_bc_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_commitments
14104: l_rec_pkt_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_packets
14105:
14106: IF p_comm_fc_req ='Y' OR p_act_fc_req = 'Y' THEN
14107:
14108: IF PG_DEBUG = 'Y' THEN
14186:
14187: FOR i IN g_bc_packet_id_tbl.FIRST..g_bc_packet_id_tbl.LAST LOOP
14188:
14189: -- Generate a packet identifier for RAW record that
14190: -- needs to be inserted into Pa BC Packets
14191:
14192: IF PG_DEBUG = 'Y' THEN
14193: log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Generating new Bc packet Id for reversing RAW act and commitment line');
14194: END IF;
14206: IF PG_DEBUG = 'Y' THEN
14207: log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Before inserting AP reversing raw and burden line from bc commitments');
14208: END IF;
14209:
14210: Insert Into Pa_Bc_Packets
14211: (Packet_Id
14212: ,Bc_Packet_Id
14213: ,Parent_Bc_Packet_Id
14214: ,Set_Of_Books_Id
14256: ,document_header_id_2
14257: ,document_distribution_type
14258: )
14259: Select p_packet_id
14260: ,decode(bcc.Parent_Bc_Packet_Id,NULL,l_Raw_Bc_Packet_id,Pa_Bc_Packets_s.NextVal)
14261: ,decode(bcc.Parent_Bc_Packet_Id,NULL,NULL,l_Raw_Bc_Packet_id)
14262: ,bcc.Set_Of_Books_Id
14263: ,G_FC_Gl_Date
14264: ,G_FC_Period_Name
14323: IF PG_DEBUG = 'Y' THEN
14324: log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Before inserting the AP raw and burden lines from Bc Packets');
14325: END IF;
14326:
14327: Insert Into Pa_Bc_Packets
14328: (Packet_Id
14329: ,Bc_Packet_Id
14330: ,Parent_Bc_Packet_Id
14331: ,Set_Of_Books_Id
14373: ,document_header_id_2
14374: ,document_distribution_type
14375: )
14376: Select p_packet_id
14377: ,decode(bcc.Parent_Bc_Packet_Id,NULL,l_Raw_Bc_Packet_id,Pa_Bc_Packets_s.NextVal)
14378: ,decode(bcc.Parent_Bc_Packet_Id,NULL,NULL,l_Raw_Bc_Packet_id)
14379: ,bcc.Set_Of_Books_Id
14380: ,G_FC_Gl_Date
14381: ,G_FC_Period_Name
14421: ,p_txn_interface_id --REL12
14422: ,bcc.encumbrance_type_id
14423: ,bcc.document_header_id_2
14424: ,bcc.document_distribution_type
14425: From Pa_Bc_Packets bcc
14426: Where bcc.packet_id = g_packet_id_tbl(i)
14427: and bcc.document_type = 'AP'
14428: -- Bug : 4962731
14429: -- : R12.PJ:XB1:QA:BC:INCORRECT AMOUNTS INTERFACED TO PROJECTS IN CASH BASED ACC
14445: IF PG_DEBUG = 'Y' THEN
14446: log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Before inserting ACTUAL raw line from bc packets');
14447: END IF;
14448:
14449: Insert Into Pa_Bc_Packets
14450: (Packet_Id
14451: ,Bc_Packet_Id
14452: ,Parent_Bc_Packet_Id
14453: ,Set_Of_Books_Id
14492: ,txn_interface_id --REL12
14493: ,vendor_id
14494: )
14495: Select p_Packet_Id
14496: ,Pa_Bc_Packets_s.NextVal
14497: ,-1
14498: ,g_sob_Id_tbl(i)
14499: ,G_FC_Gl_Date
14500: ,G_FC_Period_Name
14574: log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Before inserting adjusted ACTUAL raw line from transaction import table');
14575: END IF;
14576:
14577: FORALL i IN 1 .. g_xface_Txn_interface_tbl.count
14578: Insert Into Pa_Bc_Packets
14579: (Packet_Id
14580: ,Bc_Packet_Id
14581: ,Parent_Bc_Packet_Id
14582: ,Set_Of_Books_Id
14620: ,txn_interface_id --REL12
14621: ,vendor_id
14622: )
14623: Select p_Packet_Id
14624: ,Pa_Bc_Packets_s.NextVal
14625: ,-1
14626: ,g_sobid
14627: ,G_FC_Gl_Date
14628: ,G_FC_Period_Name
14877: p_comm_fc_req IN VARCHAR2,
14878: p_act_fc_req IN VARCHAR2,
14879: p_adj_act_fc_req IN VARCHAR2) IS
14880: CURSOR c_gen_raw_bc_pkt IS
14881: SELECT Pa_Bc_Packets_s.NextVal
14882: FROM dual;
14883:
14884: CURSOR c_get_po_LINE_id (p_po_dist_id NUMBER) IS
14885: SELECT po_line_id
14963: ,bcc.packet_id
14964: ,bcc.vendor_id
14965: -- R12 Funds management Uptake : For fixing performance issues removing the code for CC which is obsolete for R12
14966: -- Modifying code to use Index on document_header_id and document_distribution_id of bc commitments
14967: From Pa_Bc_packets bcc,
14968: po_distributions pod
14969: where bcc.document_header_id = pod.po_header_id
14970: and bcc.document_distribution_id = pod.po_distribution_id
14971: and bcc.project_id = pod.project_id -- Bug# 4479105
14980: and bcc.Parent_Bc_Packet_Id is NULL
14981: Order By bcc.packet_id;
14982:
14983: l_rec_bc_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_commitments
14984: l_rec_pkt_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_packets
14985: l_Raw_Bc_Packet_Id pa_bc_packets.bc_packet_id%TYPE;
14986: l_po_line_id po_distributions_all.po_line_id%TYPE;
14987: l_expenditure_item_date pa_expenditure_items_all.expenditure_item_date%TYPE; -- Aded for 16451280
14988:
14981: Order By bcc.packet_id;
14982:
14983: l_rec_bc_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_commitments
14984: l_rec_pkt_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_packets
14985: l_Raw_Bc_Packet_Id pa_bc_packets.bc_packet_id%TYPE;
14986: l_po_line_id po_distributions_all.po_line_id%TYPE;
14987: l_expenditure_item_date pa_expenditure_items_all.expenditure_item_date%TYPE; -- Aded for 16451280
14988:
14989: BEGIN
15008:
15009: -- Code to derive values for variables which will decide the flow of this procedure
15010:
15011: l_rec_bc_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_commitments
15012: l_rec_pkt_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_packets
15013:
15014: IF p_comm_fc_req ='Y' OR p_act_fc_req = 'Y' THEN
15015:
15016: IF PG_DEBUG = 'Y' THEN
15104: FOR i IN g_bc_packet_id_tbl.FIRST..g_bc_packet_id_tbl.LAST LOOP
15105:
15106:
15107: -- Generate a packet identifier for RAW record that
15108: -- needs to be inserted into Pa BC Packets
15109:
15110: IF PG_DEBUG = 'Y' THEN
15111: log_message('log_message: ' || 'In insert_po_bc_packets_auto - Generating new Bc packet Id for reversing RAW act and commitment line');
15112: END IF;
15124: IF PG_DEBUG = 'Y' THEN
15125: log_message('log_message: ' || 'In insert_po_bc_packets_auto - Before inserting PO reversing raw and burden line from bc commitments');
15126: END IF;
15127:
15128: Insert Into Pa_Bc_Packets
15129: (Packet_Id
15130: ,Bc_Packet_Id
15131: ,Parent_Bc_Packet_Id
15132: ,Set_Of_Books_Id
15174: ,document_header_id_2
15175: ,document_distribution_type
15176: )
15177: Select p_packet_id
15178: ,decode(bcc.Parent_Bc_Packet_Id,NULL,l_Raw_Bc_Packet_id,Pa_Bc_Packets_s.NextVal)
15179: ,decode(bcc.Parent_Bc_Packet_Id,NULL,NULL,l_Raw_Bc_Packet_id)
15180: ,bcc.Set_Of_Books_Id
15181: ,G_FC_Gl_Date
15182: ,G_FC_Period_Name
15254: IF PG_DEBUG = 'Y' THEN
15255: log_message('log_message: ' || 'In insert_po_bc_packets_auto - Before inserting the PO raw and burden lines from Bc Packets');
15256: END IF;
15257:
15258: Insert Into Pa_Bc_Packets
15259: (Packet_Id
15260: ,Bc_Packet_Id
15261: ,Parent_Bc_Packet_Id
15262: ,Set_Of_Books_Id
15304: ,document_header_id_2
15305: ,document_distribution_type
15306: )
15307: Select p_packet_id
15308: ,decode(bcc.Parent_Bc_Packet_Id,NULL,l_Raw_Bc_Packet_id,Pa_Bc_Packets_s.NextVal)
15309: ,decode(bcc.Parent_Bc_Packet_Id,NULL,NULL,l_Raw_Bc_Packet_id)
15310: ,bcc.Set_Of_Books_Id
15311: ,G_FC_Gl_Date
15312: ,G_FC_Period_Name
15362: ,p_txn_interface_id --REL12
15363: ,bcc.encumbrance_type_id
15364: ,bcc.document_header_id_2
15365: ,bcc.document_distribution_type
15366: From Pa_Bc_Packets bcc
15367: Where bcc.packet_id = g_packet_id_tbl(i)
15368: and bcc.bc_packet_id in (SELECT bcc1.bc_packet_id
15369: FROM PA_BC_PACKETS bcc1
15370: WHERE bcc1.packet_id = g_packet_id_tbl(i)
15365: ,bcc.document_distribution_type
15366: From Pa_Bc_Packets bcc
15367: Where bcc.packet_id = g_packet_id_tbl(i)
15368: and bcc.bc_packet_id in (SELECT bcc1.bc_packet_id
15369: FROM PA_BC_PACKETS bcc1
15370: WHERE bcc1.packet_id = g_packet_id_tbl(i)
15371: and bcc1.parent_bc_packet_id = g_bc_packet_id_tbl(i)
15372: and bcc1.Status_Code in ('A','C')
15373: UNIOn ALL
15388: IF PG_DEBUG = 'Y' THEN
15389: log_message('log_message: ' || 'In insert_po_bc_packets_auto - Before inserting ACTUAL raw line from bc packets');
15390: END IF;
15391:
15392: Insert Into Pa_Bc_Packets
15393: (Packet_Id
15394: ,Bc_Packet_Id
15395: ,Parent_Bc_Packet_Id
15396: ,Set_Of_Books_Id
15435: ,txn_interface_id --REL12
15436: ,vendor_id
15437: )
15438: Select p_Packet_Id
15439: ,Pa_Bc_Packets_s.NextVal
15440: ,-1
15441: ,g_sob_Id_tbl(i)
15442: ,G_FC_Gl_Date
15443: ,G_FC_Period_Name
15511: log_message('log_message: ' || 'In insert_po_bc_packets_auto - Before inserting adjusted ACTUAL raw line from transaction import table');
15512: END IF;
15513:
15514: FORALL i IN 1 .. g_xface_Txn_interface_tbl.count
15515: Insert Into Pa_Bc_Packets
15516: (Packet_Id
15517: ,Bc_Packet_Id
15518: ,Parent_Bc_Packet_Id
15519: ,Set_Of_Books_Id
15557: ,txn_interface_id --REL12
15558: ,vendor_id
15559: )
15560: Select p_Packet_Id
15561: ,Pa_Bc_Packets_s.NextVal
15562: ,-1
15563: ,g_sobid
15564: ,G_FC_Gl_Date
15565: ,G_FC_Period_Name