[Home] [Help]
281: --End PA-K Changes
282:
283: --Bug 2905892
284: G_FC_Gl_Date DATE;
285: G_FC_Period_Name pa_bc_packets.period_name%Type;
286: G_FC_Period_Year pa_bc_packets.period_year%type; --REL12
287: G_PrevFCGLDate DATE;
288: G_PrevFCPdName pa_bc_packets.period_name%Type;
289:
282:
283: --Bug 2905892
284: G_FC_Gl_Date DATE;
285: G_FC_Period_Name pa_bc_packets.period_name%Type;
286: G_FC_Period_Year pa_bc_packets.period_year%type; --REL12
287: G_PrevFCGLDate DATE;
288: G_PrevFCPdName pa_bc_packets.period_name%Type;
289:
290: PROCEDURE tr_import_funds_check (p_pa_date IN DATE,
284: G_FC_Gl_Date DATE;
285: G_FC_Period_Name pa_bc_packets.period_name%Type;
286: G_FC_Period_Year pa_bc_packets.period_year%type; --REL12
287: G_PrevFCGLDate DATE;
288: G_PrevFCPdName pa_bc_packets.period_name%Type;
289:
290: PROCEDURE tr_import_funds_check (p_pa_date IN DATE,
291: p_txn_source IN VARCHAR2,
292: p_acct_raw_cost IN NUMBER,
1548:
1549: Cursor c_check_fail(c_packet_id NUMBER) is
1550: Select 'X'
1551: from Dual
1552: Where exists (select 'Y' from pa_bc_packets
1553: Where packet_id = c_packet_id
1554: and substr(nvl(result_code, 'P'),1,1) = 'F');
1555:
1556:
5260: log_message('log_message: ' || pa_debug.G_err_Stage);
5261: END IF;
5262:
5263: --Bug 2905892
5264: --Assign txn gl_Date for insert of the reversing and EXP lines into pa_bc_packets
5265: G_FC_Gl_Date := X_Gl_Date;
5266:
5267: tr_import_funds_check (p_pa_date => pa_date,
5268: p_txn_source => X_trx_src,
9570: b.cdl_system_reference4,
9571: a.project_id,
9572: a.budget_line_id ,
9573: a.budget_version_id
9574: from pa_bc_packets a,
9575: pa_transaction_interface b
9576: where a.txn_interface_id = b.txn_interface_id
9577: and a.document_header_id = b.cdl_system_reference2
9578: and a.document_distribution_id = decode(p_calling_mode, 'AP VARIANCE',b.cdl_system_reference5
9595: b.cdl_system_reference4,
9596: a.project_id,
9597: a.budget_line_id ,
9598: a.budget_version_id
9599: from pa_bc_packets a,
9600: pa_transaction_interface b,-- moac changes
9601: pa_expenditure_items c
9602: where a.txn_interface_id = b.txn_interface_id
9603: and a.document_header_id = b.expenditure_item_id
9622: b.cdl_system_reference4,
9623: a.project_id,
9624: a.budget_line_id ,
9625: a.budget_version_id
9626: from pa_bc_packets a,
9627: pa_transaction_interface b -- moac changes
9628: where a.txn_interface_id = b.txn_interface_id
9629: and a.document_header_id = b.cdl_system_reference2
9630: and a.document_distribution_id = b.cdl_system_reference5
9642: b.cdl_system_reference4,
9643: a.project_id,
9644: a.budget_line_id ,
9645: a.budget_version_id
9646: from pa_bc_packets a,
9647: pa_transaction_interface b, -- moac changes
9648: ap_invoice_distributions c,
9649: po_distributions d
9650: where a.txn_interface_id = b.txn_interface_id
9663: CURSOR c_exp_item_id IS
9664: SELECT txn.expenditure_item_id ,
9665: pkt.bc_packet_id
9666: FROM pa_transaction_interface txn,-- moac changes
9667: pa_bc_packets pkt
9668: WHERE pkt.txn_interface_id = txn.txn_interface_id
9669: and pkt.request_id = g_request_id
9670: and txn.transaction_status_code = 'A'
9671: and pkt.status_code = 'P'
9676: l_packet_id NUMBER ;
9677: l_sys_ref2 NUMBER ;
9678: l_sys_ref3 NUMBER ;
9679: l_sys_ref4 VARCHAR2(30) ;
9680: l_doc_dist_id pa_bc_packets.document_distribution_id%TYPE ;
9681: l_ExpItemId_temp_Tab PA_PLSQL_DATATYPES.Num15TabTyp;
9682: l_budget_ccid NUMBER ;
9683: l_old_pkt NUMBER := 0;
9684: l_project_id NUMBER ;
9681: l_ExpItemId_temp_Tab PA_PLSQL_DATATYPES.Num15TabTyp;
9682: l_budget_ccid NUMBER ;
9683: l_old_pkt NUMBER := 0;
9684: l_project_id NUMBER ;
9685: l_bc_pkt_id pa_bc_packets.bc_packet_id%TYPE;
9686: l_exp_item_id pa_expenditure_items_all.expenditure_item_id%TYPE;
9687: l_budget_line_id pa_bc_packets.budget_line_id%TYPE;
9688: l_budget_version_id pa_bc_packets.budget_version_id%TYPE;
9689:
9683: l_old_pkt NUMBER := 0;
9684: l_project_id NUMBER ;
9685: l_bc_pkt_id pa_bc_packets.bc_packet_id%TYPE;
9686: l_exp_item_id pa_expenditure_items_all.expenditure_item_id%TYPE;
9687: l_budget_line_id pa_bc_packets.budget_line_id%TYPE;
9688: l_budget_version_id pa_bc_packets.budget_version_id%TYPE;
9689:
9690:
9691: PROCEDURE Upd_Sts_Enc_Bal(p_packet_id in number,
9684: l_project_id NUMBER ;
9685: l_bc_pkt_id pa_bc_packets.bc_packet_id%TYPE;
9686: l_exp_item_id pa_expenditure_items_all.expenditure_item_id%TYPE;
9687: l_budget_line_id pa_bc_packets.budget_line_id%TYPE;
9688: l_budget_version_id pa_bc_packets.budget_version_id%TYPE;
9689:
9690:
9691: PROCEDURE Upd_Sts_Enc_Bal(p_packet_id in number,
9692: x_return_status out NOCOPY varchar2 ) is
9705:
9706: -- R12 funds management : 'C' status code has been obsoleted
9707: l_records_updated := 0;
9708:
9709: -- Bug 5372480 : Records of pa_bc_packets are marked to 'A' status before calling pa_funds_control_pkg.UPD_BDGT_ENCUM_BAL
9710: -- because the CURSOR bdgt_encum_details in pa_funds_control_pkg.UPD_BDGT_ENCUM_BAL queries for 'A' status records from pa_bc_packets.
9711: -- Also there is no need to loop for distinct projects earlier being fetched by cursor c_bcpkt_projects.
9712:
9713: update pa_bc_packets a
9706: -- R12 funds management : 'C' status code has been obsoleted
9707: l_records_updated := 0;
9708:
9709: -- Bug 5372480 : Records of pa_bc_packets are marked to 'A' status before calling pa_funds_control_pkg.UPD_BDGT_ENCUM_BAL
9710: -- because the CURSOR bdgt_encum_details in pa_funds_control_pkg.UPD_BDGT_ENCUM_BAL queries for 'A' status records from pa_bc_packets.
9711: -- Also there is no need to loop for distinct projects earlier being fetched by cursor c_bcpkt_projects.
9712:
9713: update pa_bc_packets a
9714: set a.status_code = 'A'
9709: -- Bug 5372480 : Records of pa_bc_packets are marked to 'A' status before calling pa_funds_control_pkg.UPD_BDGT_ENCUM_BAL
9710: -- because the CURSOR bdgt_encum_details in pa_funds_control_pkg.UPD_BDGT_ENCUM_BAL queries for 'A' status records from pa_bc_packets.
9711: -- Also there is no need to loop for distinct projects earlier being fetched by cursor c_bcpkt_projects.
9712:
9713: update pa_bc_packets a
9714: set a.status_code = 'A'
9715: where a.packet_id = p_packet_id
9716: and a.request_id = g_request_id
9717: and a.status_code = 'P';
9781: END IF;
9782: exit;
9783: end if;
9784:
9785: UPDATE pa_bc_packets
9786: SET document_header_id = l_exp_item_id,
9787: reference1 = l_exp_item_id
9788: WHERE bc_packet_id = l_bc_pkt_id;
9789: END LOOP;
10076: close select_disc_packets;
10077: end if;
10078:
10079: /* Start of bug 3239837 */
10080: pa_debug.G_err_stage := 'Update rejected interface records in pa_bc_packets to T';
10081: pa_cc_utils.log_message(pa_debug.G_err_Stage);
10082:
10083: update pa_bc_packets
10084: set status_code = 'T',
10079: /* Start of bug 3239837 */
10080: pa_debug.G_err_stage := 'Update rejected interface records in pa_bc_packets to T';
10081: pa_cc_utils.log_message(pa_debug.G_err_Stage);
10082:
10083: update pa_bc_packets
10084: set status_code = 'T',
10085: result_code = 'F140'
10086: where request_id = g_request_id
10087: and status_code = 'P'
10095:
10096: pa_debug.G_err_stage := 'In stage 1 Updated count to T = '|| SQL%ROWCOUNT;
10097: pa_cc_utils.log_message(pa_debug.G_err_Stage);
10098:
10099: update pa_bc_packets
10100: set status_code = 'T',
10101: result_code = 'F140'
10102: where request_id = g_request_id
10103: and status_code = 'P'
10127: IF PG_DEBUG = 'Y' THEN
10128: log_message('log_message: Before update to T');
10129: END IF;
10130:
10131: update pa_bc_packets
10132: set status_code = 'T',
10133: result_code = 'F140'
10134: where status_code = 'P'
10135: and request_id = G_REQUEST_ID;
10195: --R12 Funds Management Uptake : Deleted variables defined for storing encumbrance types
10196: l_project_id NUMBER(15);
10197: l_base_qv NUMBER;
10198: l_base_av NUMBER;
10199: l_period_year pa_bc_packets.period_year%type; --REL12
10200: l_inv_type ap_invoices_all.invoice_type_lookup_code%TYPE;
10201: l_line_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%TYPE;
10202:
10203: -- R12 AP lines uptake : Prepayment changes :Cursor to fetch AP related details.
10219: --Derive Period_Name for the given GL_Date from GL_Period_Statuses
10220: --for Application_Id 101.
10221: --Funds checking is done using GL periods (application id = 101)
10222: FUNCTION Get_FC_Period_Name(P_GL_Date IN DATE) RETURN VARCHAR2 IS
10223: l_Period_Name pa_bc_packets.period_name%type;
10224: BEGIN
10225:
10226: If P_GL_Date = G_PrevFCGlDate Then
10227:
10260: END IF;
10261:
10262: --
10263: -- 1. Generate a packet identifier for all the records that
10264: -- needs to be inserted into Pa BC Packets
10265: --
10266: IF PG_DEBUG = 'Y' THEN
10267: pa_debug.G_err_stage := 'Generateing new packet Id';
10268: log_message('log_message: ' || pa_debug.G_err_Stage);
10276: rather than in the beginning
10277: */
10278:
10279: --Bug 2905892
10280: --Get the GL Period Name from txn's gl_date. This will be inserted into pa_bc_packets
10281: --for both the reversing raw and burden lines and also the EXP lines.
10282: --Funds Check API will derive the GL_Date based on the Period_Name, so what
10283: --we insert for gl_date is irrelevant here.
10284: IF PG_DEBUG = 'Y' THEN
10786: /* Bug 5731450 : The following two select statements are modified to also fetch the PO exchange rate. */
10787:
10788: select sum(nvl(accounted_dr,0)-nvl(accounted_cr,0)),SUM(POD.rate)/SUM(1)
10789: into l_pkt_summ,l_pkt_rate
10790: from pa_bc_packets bcc,
10791: po_distributions pod --Added for Bug#3693974
10792: where pod.po_header_id = p_sys_ref2
10793: and pod.po_distribution_id = p_sys_ref3
10794: and ((
11098: IF PG_DEBUG = 'Y' THEN
11099: log_message('log_message: ' || 'Inside Upd_PktSts_Fatal');
11100: END IF;
11101:
11102: update pa_bc_packets
11103: set status_code = 'T',
11104: result_code = 'F140'
11105: where status_code = 'P'
11106: and request_id = P_REQUEST_ID;
11134: IF PG_DEBUG = 'Y' THEN
11135: log_message('log_message: ' || 'Inside Upd_PktSts,packet_id = '|| p_packet_id);
11136: END IF;
11137:
11138: update pa_bc_packets
11139: set status_code = 'T',
11140: result_code = 'F140'
11141: where status_code = 'P'
11142: --Bug 3592289 changed p_packet_id to packet_id
11699:
11700: -- Fetch BC Pkt Raw outstanding amount
11701: CURSOR C_get_raw_pkt_amt IS
11702: select sum(nvl(accounted_dr,0)-nvl(accounted_cr,0))
11703: from pa_bc_packets bcc
11704: where bcc.document_type = 'AP'
11705: and bcc.document_header_id = p_sys_ref2
11706: and bcc.document_distribution_id = p_sys_ref5
11707: and ( (bcc.Status_Code in('A','C'))
12094: END IF;
12095: RAISE;
12096: END insert_ap_bc_packets;
12097:
12098: -- This procedure inserts records into pa_bc_packets for relieving commitment raw and burden
12099: -- and also for reserving raw and burden against actual.
12100: -- 1. IF p_fc_document_type = 'CMT'/'ALL' then we need to relieve commitment amount.
12101: -- 2. If commitment exists in pa_bc_commitments (i.e. sweeper process if run) then
12102: -- insert records into pa_bc_packets to relieve the raw and burden commitment
12098: -- This procedure inserts records into pa_bc_packets for relieving commitment raw and burden
12099: -- and also for reserving raw and burden against actual.
12100: -- 1. IF p_fc_document_type = 'CMT'/'ALL' then we need to relieve commitment amount.
12101: -- 2. If commitment exists in pa_bc_commitments (i.e. sweeper process if run) then
12102: -- insert records into pa_bc_packets to relieve the raw and burden commitment
12103: -- amounts lying in pa_bc_commitments.
12104: -- else
12105: -- insert records into pa_bc_packets to relieve the raw and burden commitment
12106: -- amounts lying in pa_bc_packets.
12101: -- 2. If commitment exists in pa_bc_commitments (i.e. sweeper process if run) then
12102: -- insert records into pa_bc_packets to relieve the raw and burden commitment
12103: -- amounts lying in pa_bc_commitments.
12104: -- else
12105: -- insert records into pa_bc_packets to relieve the raw and burden commitment
12106: -- amounts lying in pa_bc_packets.
12107: -- end if;
12108: -- 3.p_document type = 'ALL' and p_adj_exp_item_id IS NULL implies its a regular
12109: -- Payable Invoice getting interfaced to Projects. In this case
12102: -- insert records into pa_bc_packets to relieve the raw and burden commitment
12103: -- amounts lying in pa_bc_commitments.
12104: -- else
12105: -- insert records into pa_bc_packets to relieve the raw and burden commitment
12106: -- amounts lying in pa_bc_packets.
12107: -- end if;
12108: -- 3.p_document type = 'ALL' and p_adj_exp_item_id IS NULL implies its a regular
12109: -- Payable Invoice getting interfaced to Projects. In this case
12110: -- insert records into pa_bc_packets to reserve actual raw cost amount which is equal to
12106: -- amounts lying in pa_bc_packets.
12107: -- end if;
12108: -- 3.p_document type = 'ALL' and p_adj_exp_item_id IS NULL implies its a regular
12109: -- Payable Invoice getting interfaced to Projects. In this case
12110: -- insert records into pa_bc_packets to reserve actual raw cost amount which is equal to
12111: -- commitment amount in pa_bc_commitments relieved in step 2.
12112: -- Note : parent_bc_packet_id is populated to -1 such that pa_funds_check process will
12113: -- generate burden against this actual based on latest compiled set id.
12114: -- 4.If p_document type = 'ACT'/'ALL' and p_adj_exp_item_id IS NOT NULL then it implies thats its a
12113: -- generate burden against this actual based on latest compiled set id.
12114: -- 4.If p_document type = 'ACT'/'ALL' and p_adj_exp_item_id IS NOT NULL then it implies thats its a
12115: -- reversing/cancelled Payable Invoice getting interfaced to Projects and original
12116: -- interfaced exp in project has been adjusted .
12117: -- In this case insert records into pa_bc_packets to fundscheck actual raw cost which is equal to
12118: -- amount in pa_transaction_interface for each non net zero expenditures associated with this
12119: -- invoice.
12120: -- Note : In this scenraio if non net zero expenditures associated with this invoice are not costed
12121: -- then import process (PAAPIMPB.pls) will reject the transactions with PA_EI_NOT_COST_DISTRIBUTED.
12140:
12141: PRAGMA AUTONOMOUS_TRANSACTION;
12142:
12143: CURSOR c_gen_raw_bc_pkt IS
12144: SELECT Pa_Bc_Packets_s.NextVal
12145: FROM dual;
12146:
12147: CURSOR c_get_po_dist_id IS
12148: SELECT po_distribution_id
12244: ,bcc.parent_bc_packet_id
12245: ,bcc.encumbrance_type_id
12246: ,bcc.document_header_id_2
12247: ,bcc.document_distribution_type
12248: From Pa_Bc_Packets bcc
12249: where bcc.Document_Header_Id = p_sys_ref2
12250: and bcc.Document_Distribution_Id =p_sys_ref5
12251: and bcc.Status_Code in ('A','C')
12252: and bcc.document_type = 'AP'
12263: (bcc.parent_bc_packet_id is NOT null AND p_txn_source = 'AP VARIANCE')
12264: );
12265:
12266: l_rec_bc_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_commitments
12267: l_rec_pkt_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_packets
12268: L_RAW_BC_PACKET_ID NUMBER;
12269: l_po_dist_id ap_invoice_distributions.po_distribution_id%TYPE;
12270: l_po_line_id po_distributions_all.po_line_id%TYPE;
12271:
12271:
12272:
12273: CURSOR c_curr_raw_pkt IS
12274: SELECT bc_packet_id
12275: FROM pa_bc_packets
12276: WHERE packet_id = p_packet_id
12277: AND Document_Header_Id = p_sys_ref2
12278: AND Document_Distribution_Id =p_sys_ref5
12279: AND parent_bc_packet_id IS NULL
12308: END IF;
12309:
12310: -- Code to derive values for variables which will decide the flow of this procedure
12311: l_rec_bc_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_commitments
12312: l_rec_pkt_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_packets
12313:
12314: IF p_comm_fc_req ='Y' OR p_act_fc_req = 'Y' THEN
12315:
12316: IF PG_DEBUG = 'Y' THEN
12410: IF p_comm_fc_req = 'Y' THEN
12411:
12412: -- Logic to insert commitment relieving raw and burden records
12413: FORALL i IN 1 .. g_bc_packet_id_tbl.count
12414: Insert Into Pa_Bc_Packets
12415: (Packet_Id
12416: ,Bc_Packet_Id
12417: ,Parent_Bc_Packet_Id
12418: ,Set_Of_Books_Id
12528: 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);
12529: END IF;
12530:
12531: -- Update raw records with new bc_packet_id
12532: UPDATE Pa_Bc_Packets
12533: SET bc_packet_id = l_Raw_Bc_Packet_Id
12534: WHERE packet_id = p_packet_id
12535: AND bc_packet_id = c_cur.bc_packet_id;
12536:
12538: log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - NUmber of raw packets updated with new bc_packet_id '||SQL%ROWCOUNT);
12539: END IF;
12540:
12541: -- Update burden records with new bc_packet_id abd parent_bc_packet_id
12542: UPDATE Pa_Bc_Packets
12543: SET bc_packet_id = Pa_Bc_Packets_s.NextVal
12544: ,parent_bc_packet_id = l_Raw_Bc_Packet_Id
12545: WHERE packet_id = p_packet_id
12546: AND parent_bc_packet_id = c_cur.bc_packet_id;
12539: END IF;
12540:
12541: -- Update burden records with new bc_packet_id abd parent_bc_packet_id
12542: UPDATE Pa_Bc_Packets
12543: SET bc_packet_id = Pa_Bc_Packets_s.NextVal
12544: ,parent_bc_packet_id = l_Raw_Bc_Packet_Id
12545: WHERE packet_id = p_packet_id
12546: AND parent_bc_packet_id = c_cur.bc_packet_id;
12547:
12562:
12563: FOR i IN 1 .. g_bc_packet_id_tbl.count LOOP
12564:
12565:
12566: Insert Into Pa_Bc_Packets
12567: (Packet_Id
12568: ,Bc_Packet_Id
12569: ,Parent_Bc_Packet_Id
12570: ,Set_Of_Books_Id
12608: ,reference3
12609: ,txn_interface_id --REL12
12610: )
12611: Select p_Packet_Id
12612: ,Pa_Bc_Packets_s.NextVal
12613: ,-1
12614: ,g_sob_Id_tbl(i)
12615: ,G_FC_Gl_Date
12616: ,G_FC_Period_Name
12672: -- bc_commitments/bc_packets for same inv distribution, hence the global varaibles will have
12673: -- multiple distributions.But there will be only one ap discount line for both item amount +
12674: -- qty/amt varaince. Hence actuals need to reserved only once.
12675: /* Bug 5984525 : The following code is modified so that ACTUAL lines are inserted correctly
12676: into pa bc packets while interfacing discounts to Projects. */
12677: IF p_txn_source = 'AP DISCOUNTS' AND SQL%ROWCOUNT = 1 THEN
12678: IF PG_DEBUG = 'Y' THEN
12679: log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - Inserted 1 ACTUAL raw line(s) into bc packets');
12680: END IF;
12702: log_message('log_message: ' || 'In insert_ap_bc_pkt_autonomous - Before inserting adjusted ACTUAL raw line from transaction import table');
12703: END IF;
12704:
12705: FORALL i IN 1 .. g_xface_Txn_interface_tbl.count
12706: Insert Into Pa_Bc_Packets
12707: (Packet_Id
12708: ,Bc_Packet_Id
12709: ,Parent_Bc_Packet_Id
12710: ,Set_Of_Books_Id
12747: ,reference3
12748: ,txn_interface_id --REL12
12749: )
12750: Select p_Packet_Id
12751: ,Pa_Bc_Packets_s.NextVal
12752: ,-1
12753: ,g_sobid
12754: ,G_FC_Gl_Date
12755: ,G_FC_Period_Name
12992: END IF;
12993: RAISE;
12994: END insert_cash_ap_bc_packets;
12995:
12996: -- This procedure inserts records into pa_bc_packets for relieving commitment raw and burden
12997: -- and also for reserving raw and burden against actual.
12998:
12999: -- Parameter values : p_packet_id - Packet Id to be inserted
13000: -- p_sys_ref2 - Invoice Id
13024:
13025: PRAGMA AUTONOMOUS_TRANSACTION;
13026:
13027: CURSOR c_gen_raw_bc_pkt IS
13028: SELECT Pa_Bc_Packets_s.NextVal
13029: FROM dual;
13030:
13031: CURSOR c_get_po_dist_id IS
13032: SELECT po_distribution_id
13100: ,bcc.Org_Id
13101: ,bcc.Pa_Date
13102: ,bcc.bc_packet_id
13103: ,bcc.packet_id
13104: From Pa_Bc_Packets bcc
13105: where bcc.Document_Header_Id = p_sys_ref2
13106: and bcc.Document_Distribution_Id = p_sys_ref5
13107: and bcc.Status_Code in ('A','C')
13108: and bcc.Parent_Bc_Packet_Id is NULL
13112: -- R12 Ap lines Uptake : Added for cash based accounting which can have multiple packets associated witha distribution.
13113: and rownum = 1 ;
13114:
13115: l_rec_bc_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_commitments
13116: l_rec_pkt_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_packets
13117: l_Raw_Bc_Packet_Id pa_bc_packets.bc_packet_id%TYPE;
13118: l_po_dist_id ap_invoice_distributions.po_distribution_id%TYPE;
13119: l_po_line_id po_distributions_all.po_line_id%TYPE;
13120:
13113: and rownum = 1 ;
13114:
13115: l_rec_bc_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_commitments
13116: l_rec_pkt_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_packets
13117: l_Raw_Bc_Packet_Id pa_bc_packets.bc_packet_id%TYPE;
13118: l_po_dist_id ap_invoice_distributions.po_distribution_id%TYPE;
13119: l_po_line_id po_distributions_all.po_line_id%TYPE;
13120:
13121: BEGIN
13146:
13147: -- Code to derive values for variables which will decide the flow of this procedure
13148:
13149: l_rec_bc_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_commitments
13150: l_rec_pkt_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_packets
13151:
13152: IF p_comm_fc_req ='Y' OR p_act_fc_req = 'Y' THEN
13153:
13154: IF PG_DEBUG = 'Y' THEN
13232:
13233: FOR i IN g_bc_packet_id_tbl.FIRST..g_bc_packet_id_tbl.LAST LOOP
13234:
13235: -- Generate a packet identifier for RAW record that
13236: -- needs to be inserted into Pa BC Packets
13237:
13238: IF PG_DEBUG = 'Y' THEN
13239: log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Generating new Bc packet Id for reversing RAW act and commitment line');
13240: END IF;
13252: IF PG_DEBUG = 'Y' THEN
13253: log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Before inserting AP reversing raw and burden line from bc commitments');
13254: END IF;
13255:
13256: Insert Into Pa_Bc_Packets
13257: (Packet_Id
13258: ,Bc_Packet_Id
13259: ,Parent_Bc_Packet_Id
13260: ,Set_Of_Books_Id
13302: ,document_header_id_2
13303: ,document_distribution_type
13304: )
13305: Select p_packet_id
13306: ,decode(bcc.Parent_Bc_Packet_Id,NULL,l_Raw_Bc_Packet_id,Pa_Bc_Packets_s.NextVal)
13307: ,decode(bcc.Parent_Bc_Packet_Id,NULL,NULL,l_Raw_Bc_Packet_id)
13308: ,bcc.Set_Of_Books_Id
13309: ,G_FC_Gl_Date
13310: ,G_FC_Period_Name
13369: IF PG_DEBUG = 'Y' THEN
13370: log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Before inserting the AP raw and burden lines from Bc Packets');
13371: END IF;
13372:
13373: Insert Into Pa_Bc_Packets
13374: (Packet_Id
13375: ,Bc_Packet_Id
13376: ,Parent_Bc_Packet_Id
13377: ,Set_Of_Books_Id
13419: ,document_header_id_2
13420: ,document_distribution_type
13421: )
13422: Select p_packet_id
13423: ,decode(bcc.Parent_Bc_Packet_Id,NULL,l_Raw_Bc_Packet_id,Pa_Bc_Packets_s.NextVal)
13424: ,decode(bcc.Parent_Bc_Packet_Id,NULL,NULL,l_Raw_Bc_Packet_id)
13425: ,bcc.Set_Of_Books_Id
13426: ,G_FC_Gl_Date
13427: ,G_FC_Period_Name
13467: ,p_txn_interface_id --REL12
13468: ,bcc.encumbrance_type_id
13469: ,bcc.document_header_id_2
13470: ,bcc.document_distribution_type
13471: From Pa_Bc_Packets bcc
13472: Where bcc.packet_id = g_packet_id_tbl(i)
13473: and bcc.document_type = 'AP'
13474: -- Bug : 4962731
13475: -- : R12.PJ:XB1:QA:BC:INCORRECT AMOUNTS INTERFACED TO PROJECTS IN CASH BASED ACC
13491: IF PG_DEBUG = 'Y' THEN
13492: log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Before inserting ACTUAL raw line from bc packets');
13493: END IF;
13494:
13495: Insert Into Pa_Bc_Packets
13496: (Packet_Id
13497: ,Bc_Packet_Id
13498: ,Parent_Bc_Packet_Id
13499: ,Set_Of_Books_Id
13537: ,reference3
13538: ,txn_interface_id --REL12
13539: )
13540: Select p_Packet_Id
13541: ,Pa_Bc_Packets_s.NextVal
13542: ,-1
13543: ,g_sob_Id_tbl(i)
13544: ,G_FC_Gl_Date
13545: ,G_FC_Period_Name
13618: log_message('log_message: ' || 'In insert_cash_ap_bc_pkt_auto - Before inserting adjusted ACTUAL raw line from transaction import table');
13619: END IF;
13620:
13621: FORALL i IN 1 .. g_xface_Txn_interface_tbl.count
13622: Insert Into Pa_Bc_Packets
13623: (Packet_Id
13624: ,Bc_Packet_Id
13625: ,Parent_Bc_Packet_Id
13626: ,Set_Of_Books_Id
13663: ,reference3
13664: ,txn_interface_id --REL12
13665: )
13666: Select p_Packet_Id
13667: ,Pa_Bc_Packets_s.NextVal
13668: ,-1
13669: ,g_sobid
13670: ,G_FC_Gl_Date
13671: ,G_FC_Period_Name
13917: p_comm_fc_req IN VARCHAR2,
13918: p_act_fc_req IN VARCHAR2,
13919: p_adj_act_fc_req IN VARCHAR2) IS
13920: CURSOR c_gen_raw_bc_pkt IS
13921: SELECT Pa_Bc_Packets_s.NextVal
13922: FROM dual;
13923:
13924: CURSOR c_get_po_LINE_id (p_po_dist_id NUMBER) IS
13925: SELECT po_line_id
13994: ,bcc.bc_packet_id
13995: ,bcc.packet_id
13996: -- R12 Funds management Uptake : For fixing performance issues removing the code for CC which is obsolete for R12
13997: -- Modifying code to use Index on document_header_id and document_distribution_id of bc commitments
13998: From Pa_Bc_packets bcc,
13999: po_distributions pod
14000: where bcc.document_header_id = pod.po_header_id
14001: and bcc.document_distribution_id = pod.po_distribution_id
14002: and bcc.project_id = pod.project_id -- Bug# 4479105
14010: and bcc.Parent_Bc_Packet_Id is NULL
14011: Order By bcc.packet_id;
14012:
14013: l_rec_bc_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_commitments
14014: l_rec_pkt_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_packets
14015: l_Raw_Bc_Packet_Id pa_bc_packets.bc_packet_id%TYPE;
14016: l_po_line_id po_distributions_all.po_line_id%TYPE;
14017:
14018: BEGIN
14011: Order By bcc.packet_id;
14012:
14013: l_rec_bc_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_commitments
14014: l_rec_pkt_comm_exists VARCHAR2(1); -- Variable to identify whether commitment record exists in pa_bc_packets
14015: l_Raw_Bc_Packet_Id pa_bc_packets.bc_packet_id%TYPE;
14016: l_po_line_id po_distributions_all.po_line_id%TYPE;
14017:
14018: BEGIN
14019:
14037:
14038: -- Code to derive values for variables which will decide the flow of this procedure
14039:
14040: l_rec_bc_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_commitments
14041: l_rec_pkt_comm_exists:='N'; -- Variable to identify whether commitment record exists in pa_bc_packets
14042:
14043: IF p_comm_fc_req ='Y' OR p_act_fc_req = 'Y' THEN
14044:
14045: IF PG_DEBUG = 'Y' THEN
14127: FOR i IN g_bc_packet_id_tbl.FIRST..g_bc_packet_id_tbl.LAST LOOP
14128:
14129:
14130: -- Generate a packet identifier for RAW record that
14131: -- needs to be inserted into Pa BC Packets
14132:
14133: IF PG_DEBUG = 'Y' THEN
14134: log_message('log_message: ' || 'In insert_po_bc_packets_auto - Generating new Bc packet Id for reversing RAW act and commitment line');
14135: END IF;
14147: IF PG_DEBUG = 'Y' THEN
14148: log_message('log_message: ' || 'In insert_po_bc_packets_auto - Before inserting PO reversing raw and burden line from bc commitments');
14149: END IF;
14150:
14151: Insert Into Pa_Bc_Packets
14152: (Packet_Id
14153: ,Bc_Packet_Id
14154: ,Parent_Bc_Packet_Id
14155: ,Set_Of_Books_Id
14197: ,document_header_id_2
14198: ,document_distribution_type
14199: )
14200: Select p_packet_id
14201: ,decode(bcc.Parent_Bc_Packet_Id,NULL,l_Raw_Bc_Packet_id,Pa_Bc_Packets_s.NextVal)
14202: ,decode(bcc.Parent_Bc_Packet_Id,NULL,NULL,l_Raw_Bc_Packet_id)
14203: ,bcc.Set_Of_Books_Id
14204: ,G_FC_Gl_Date
14205: ,G_FC_Period_Name
14276: IF PG_DEBUG = 'Y' THEN
14277: log_message('log_message: ' || 'In insert_po_bc_packets_auto - Before inserting the PO raw and burden lines from Bc Packets');
14278: END IF;
14279:
14280: Insert Into Pa_Bc_Packets
14281: (Packet_Id
14282: ,Bc_Packet_Id
14283: ,Parent_Bc_Packet_Id
14284: ,Set_Of_Books_Id
14326: ,document_header_id_2
14327: ,document_distribution_type
14328: )
14329: Select p_packet_id
14330: ,decode(bcc.Parent_Bc_Packet_Id,NULL,l_Raw_Bc_Packet_id,Pa_Bc_Packets_s.NextVal)
14331: ,decode(bcc.Parent_Bc_Packet_Id,NULL,NULL,l_Raw_Bc_Packet_id)
14332: ,bcc.Set_Of_Books_Id
14333: ,G_FC_Gl_Date
14334: ,G_FC_Period_Name
14382: ,p_txn_interface_id --REL12
14383: ,bcc.encumbrance_type_id
14384: ,bcc.document_header_id_2
14385: ,bcc.document_distribution_type
14386: From Pa_Bc_Packets bcc
14387: Where bcc.packet_id = g_packet_id_tbl(i)
14388: and bcc.bc_packet_id in (SELECT bcc1.bc_packet_id
14389: FROM PA_BC_PACKETS bcc1
14390: WHERE bcc1.packet_id = g_packet_id_tbl(i)
14385: ,bcc.document_distribution_type
14386: From Pa_Bc_Packets bcc
14387: Where bcc.packet_id = g_packet_id_tbl(i)
14388: and bcc.bc_packet_id in (SELECT bcc1.bc_packet_id
14389: FROM PA_BC_PACKETS bcc1
14390: WHERE bcc1.packet_id = g_packet_id_tbl(i)
14391: and bcc1.parent_bc_packet_id = g_bc_packet_id_tbl(i)
14392: and bcc1.Status_Code in ('A','C')
14393: UNIOn ALL
14408: IF PG_DEBUG = 'Y' THEN
14409: log_message('log_message: ' || 'In insert_po_bc_packets_auto - Before inserting ACTUAL raw line from bc packets');
14410: END IF;
14411:
14412: Insert Into Pa_Bc_Packets
14413: (Packet_Id
14414: ,Bc_Packet_Id
14415: ,Parent_Bc_Packet_Id
14416: ,Set_Of_Books_Id
14454: ,reference3
14455: ,txn_interface_id --REL12
14456: )
14457: Select p_Packet_Id
14458: ,Pa_Bc_Packets_s.NextVal
14459: ,-1
14460: ,g_sob_Id_tbl(i)
14461: ,G_FC_Gl_Date
14462: ,G_FC_Period_Name
14527: log_message('log_message: ' || 'In insert_po_bc_packets_auto - Before inserting adjusted ACTUAL raw line from transaction import table');
14528: END IF;
14529:
14530: FORALL i IN 1 .. g_xface_Txn_interface_tbl.count
14531: Insert Into Pa_Bc_Packets
14532: (Packet_Id
14533: ,Bc_Packet_Id
14534: ,Parent_Bc_Packet_Id
14535: ,Set_Of_Books_Id
14572: ,reference3
14573: ,txn_interface_id --REL12
14574: )
14575: Select p_Packet_Id
14576: ,Pa_Bc_Packets_s.NextVal
14577: ,-1
14578: ,g_sobid
14579: ,G_FC_Gl_Date
14580: ,G_FC_Period_Name