[Home] [Help]
466: /* This CURSOR selects all reporting set of books enabled for the primary set of book id
467: in PA */
468:
469: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will obsoleted, replace with
470: new table gl_alc_ledger_rships_v and corresponding columns */
471:
472: /* CURSOR rsob is SELECT rep.reporting_set_of_books_id reporting_set_of_books_id ,
473: rep.reporting_currency_code, rep.conversion_type
474: FROM gl_mc_reporting_options rep, pa_implementations imp
479:
480: CURSOR rsob is SELECT rep.ledger_id reporting_set_of_books_id ,
481: rep.currency_code reporting_currency_code,
482: rep.alc_default_conv_rate_type conversion_type
483: FROM gl_alc_ledger_rships_v rep, pa_implementations imp
484: WHERE rep.source_ledger_id = imp.set_of_books_id
485: AND rep.relationship_enabled_flag = 'Y'
486: AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
487: AND rep.application_id = 275;
492: /* This CURSOR selects all reporting set of books enabled for the primary set of book id
493: in AR */
494:
495: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will obsoleted, replace with
496: new table gl_alc_ledger_rships_v and corresponding columns */
497:
498:
499: /* CURSOR rsob_ar_mc is SELECT rep.reporting_set_of_books_id reporting_set_of_books_id ,
500: rep.reporting_currency_code, rep.conversion_type
507:
508: CURSOR rsob_ar_mc is SELECT rep.ledger_id reporting_set_of_books_id ,
509: rep.currency_code reporting_currency_code,
510: rep.alc_default_conv_rate_type conversion_type
511: FROM gl_alc_ledger_rships_v rep, pa_implementations imp
512: WHERE rep.source_ledger_id = imp.set_of_books_id
513: AND rep.relationship_enabled_flag = 'Y'
514: AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
515: AND application_id = PA_FUND_REVAL_UTIL.get_ar_application_id;
1733: for the given project in primary and reporting set of book ids */
1734:
1735:
1736: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will obsoleted, replace with
1737: new table gl_alc_ledger_rships_v and corresponding columns */
1738:
1739: /* mrc migration to SLA bug 4571438
1740: CURSOR get_all_spf_lines (l_ProjectId NUMBER) is
1741: (SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
1778: PC.retention_level_code retention_level_code,
1779: PC.customer_id cust_id
1780: FROM pa_mc_sum_proj_fundings SPF_mc, pa_summary_project_fundings SPF,
1781: pa_agreements_all A, pa_project_customers PC,
1782: gl_alc_ledger_rships_v rep, pa_implementations imp
1783: WHERE SPF.project_id = l_ProjectId
1784: AND A.agreement_id = SPF.agreement_id
1785: AND PC.customer_id = A.customer_id
1786: AND PC.project_id = SPF.project_id
2535: primary and reporint sob
2536: */
2537:
2538: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
2539: new table gl_alc_ledger_rships_v and corresponding columns */
2540: /* mrc migration to SLA bug 4571438
2541: CURSOR get_all_retn_invoices IS
2542: (SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
2543: di.draft_invoice_num drft_inv_num,
2582: sum(dii_mc.amount) projfunc_bill_amount,
2583: sum(dii.funding_bill_amount) funding_bill_amount,
2584: sum(dii.inv_amount) inv_amount
2585: FROM pa_draft_invoice_items dii, pa_mc_draft_inv_items dii_mc, pa_draft_invoices di,
2586: gl_alc_ledger_rships_v rep, pa_implementations imp
2587: WHERE di.project_id = p_project_id
2588: AND di.agreement_id = p_agreement_id
2589: AND di.transfer_status_code = 'A'
2590: AND dii.project_id = di.project_id
3350: primary and reporting set of book ids */
3351:
3352:
3353: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
3354: new table gl_alc_ledger_rships_v and corresponding columns */
3355:
3356: /* mrc migration to SLA bug 4571438
3357: CURSOR get_all_proj_invoices IS
3358: (SELECT 'REGULAR-PROJ' invoice_type,
3446: 0 projfunc_retn_amount,
3447: 0 funding_retn_amount,
3448: 0 inv_retn_amount
3449: FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
3450: gl_alc_ledger_rships_v rep, pa_implementations imp
3451: WHERE di.project_id = p_project_id
3452: AND di.agreement_id = p_agreement_id
3453: AND dii.project_id = di.project_id
3454: AND dii.draft_invoice_num = di.draft_invoice_num
3491: 0 projfunc_retn_amount,
3492: 0 funding_retn_amount,
3493: 0 inv_retn_amount
3494: FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
3495: gl_alc_ledger_rships_v rep, pa_implementations imp
3496: WHERE di.project_id = p_project_id
3497: AND di.agreement_id = p_agreement_id
3498: AND dii.project_id = di.project_id
3499: AND dii.draft_invoice_num = di.draft_invoice_num
3636: /* This CURSOR is same as previous CURSOR except that ti will select for both primary and reporting set of
3637: books isd */
3638:
3639: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
3640: new table gl_alc_ledger_rships_v and corresponding columns */
3641:
3642: /* mrc migration to SLA bug 4571438 CURSOR get_all_task_invoices IS
3643: (SELECT 'REGULAR-TASK' invoice_type,
3644: PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
3744: dii.funding_bill_amount,0)) funding_retn_amount,
3745: sum(decode(dii.invoice_line_type,'RETENTION',
3746: dii.inv_amount,0)) inv_retn_amount
3747: FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
3748: gl_alc_ledger_rships_v rep, pa_implementations imp
3749: WHERE di.project_id = p_project_id
3750: AND di.agreement_id = p_agreement_id
3751: AND dii.project_id = di.project_id
3752: AND dii.draft_invoice_num = di.draft_invoice_num
3789: 0 projfunc_retn_amount,
3790: 0 funding_retn_amount,
3791: 0 inv_retn_amount
3792: FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
3793: gl_alc_ledger_rships_v rep, pa_implementations imp
3794: WHERE di.project_id = p_project_id
3795: AND di.agreement_id = p_agreement_id
3796: AND dii.project_id = di.project_id
3797: AND dii.draft_invoice_num = di.draft_invoice_num
5895:
5896: /* The following CURSOR is same as above but fetches for both primary and reporting set of books */
5897:
5898: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
5899: new table gl_alc_ledger_rships_v and corresponding columns */
5900:
5901: /* mrc migration to SLA bug 4571438
5902: CURSOR get_all_inv_total IS
5903: (SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
5915: sum(dii_mc.amount) projfunc_bill_amount,
5916: sum(dii.funding_bill_amount) funding_bill_amount,
5917: sum(dii.inv_amount) inv_amount
5918: FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii,
5919: gl_alc_ledger_rships_v rep, pa_implementations imp
5920: WHERE dii.project_id = p_project_id
5921: AND dii.draft_invoice_num = p_draft_inv_num
5922: -- AND dii.invoice_line_type <> 'RETENTION'
5923: AND rep.source_ledger_id = imp.set_of_books_id
6156: AND RDL.draft_invoice_num = DII.draft_invoice_num
6157: AND RDL.draft_invoice_item_line_num = DII.line_num;
6158:
6159: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
6160: new table gl_alc_ledger_rships_v and corresponding columns */
6161:
6162: /* mrc migration to SLA bug 4571438 CURSOR rdl_amount_all IS
6163: (SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
6164: SUM((rdl.retained_amount/rdl.bill_amount) * rdl.projfunc_bill_amount) retained_amt_pfc,
6174: SELECT RDL_MC.set_of_books_id,
6175: SUM((rdl.retained_amount/rdl.bill_amount) * rdl_mc.amount) retained_amt_pfc,
6176: SUM((rdl.retained_amount/rdl.bill_amount) * rdl.funding_bill_amount) retained_amt_fc
6177: FROM pa_cust_rev_dist_lines RDL, pa_draft_invoice_items DII, pa_mc_cust_rdl_all RDL_MC,
6178: gl_alc_ledger_rships_v rep, pa_implementations imp
6179: WHERE DII.project_id = p_project_id
6180: AND DII.draft_invoice_num = p_draft_inv_num
6181: AND NVL(DII.task_id,0) = p_task_id
6182: AND RDL.project_id = DII.project_id
6210: AND ERDL.draft_invoice_item_line_num = DII.line_num;
6211:
6212:
6213: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
6214: new table gl_alc_ledger_rships_v and corresponding columns */
6215:
6216: /* mrc migration to SLA bug 4571438 CURSOR erdl_amount_all IS
6217: (SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
6218: SUM((erdl.retained_amount/erdl.amount) * erdl.projfunc_bill_amount) retained_amt_pfc,
6229: SUM((erdl.retained_amount/erdl.amount) * ERDL_MC.amount) retained_amt_pfc,
6230: SUM((erdl.retained_amount/erdl.amount) * erdl.funding_bill_amount) retained_amt_fc
6231: FROM pa_cust_event_rdl_all ERDL, pa_draft_invoice_items DII,
6232: pa_mc_cust_event_rdl_all ERDL_MC,
6233: gl_alc_ledger_rships_v rep, pa_implementations imp
6234: WHERE DII.project_id = p_project_id
6235: AND DII.draft_invoice_num = p_draft_inv_num
6236: AND NVL(DII.task_id,0) = p_task_id
6237: AND ERDL.project_id = DII.project_id
6263: AND DII.task_id = p_task_id
6264: AND DII.event_num is not null;
6265:
6266: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
6267: new table gl_alc_ledger_rships_v and corresponding columns */
6268:
6269: /* mrc migration to SLA bug 4571438 CURSOR dii_amount_all IS
6270: (SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
6271: SUM((DII.retained_amount/DII.amount) * DII.projfunc_bill_amount) retained_amt_pfc,
6279: SELECT DII_MC.set_of_books_id,
6280: SUM((DII.retained_amount/DII.amount) * DII_MC.amount) retained_amt_pfc,
6281: SUM((DII.retained_amount/DII.amount) * DII.funding_bill_amount) retained_amt_fc
6282: FROM pa_draft_invoice_items DII, pa_mc_draft_inv_items DII_MC,
6283: gl_alc_ledger_rships_v rep, pa_implementations imp
6284: WHERE DII.project_id = p_project_id
6285: AND DII.draft_invoice_num = p_draft_inv_num
6286: AND DII.task_id = p_task_id
6287: AND rep.source_ledger_id = imp.set_of_books_id
7065: /* The following CURSOR will select all invoices for given project/agreement for
7066: primary and reporting set of book ids */
7067:
7068: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
7069: new table gl_alc_ledger_rships_v and corresponding columns */
7070:
7071: /* mrc migration to SLA bug 4571438 CURSOR get_all_proj_invoices IS
7072: (SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
7073: 0 task_id,
7090: sum(dii_mc.amount) projfunc_bill_amount,
7091: sum(dii.funding_bill_amount) funding_bill_amount
7092: FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii,
7093: pa_draft_invoices di,
7094: gl_alc_ledger_rships_v rep, pa_implementations imp
7095: WHERE di.project_id = p_project_id
7096: AND di.agreement_id = p_agreement_id
7097: AND dii.project_id = di.project_id
7098: AND dii.draft_invoice_num = di.draft_invoice_num
7135: GROUP BY dii.task_id
7136: ORDER BY task_id;
7137:
7138: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
7139: new table gl_alc_ledger_rships_v and corresponding columns */
7140:
7141: /* This CURSOR is same as previous CURSOR except that ti will select for both primary and reporting set of
7142: books isd */
7143: /* mrc migration to SLA bug 4571438 CURSOR get_all_task_invoices IS
7162: sum(dii.amount) amount,
7163: sum(dii_mc.amount) projfunc_bill_amount,
7164: sum(dii.funding_bill_amount) funding_bill_amount
7165: FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
7166: gl_alc_ledger_rships_v rep, pa_implementations imp
7167: WHERE di.project_id = p_project_id
7168: AND di.agreement_id = p_agreement_id
7169: AND dii.project_id = di.project_id
7170: AND dii.draft_invoice_num = di.draft_invoice_num