[Home] [Help]
152:
153:
154: PROCEDURE conv_acctd_amt
155: (p_pay_adj IN VARCHAR2,
156: p_adj_rec IN ar_adjustments%ROWTYPE,
157: p_app_rec IN ar_receivable_applications%ROWTYPE,
158: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type);
159:
160: PROCEDURE conv_acctd_amt_upg
158: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type);
159:
160: PROCEDURE conv_acctd_amt_upg
161: (p_pay_adj IN VARCHAR2,
162: p_adj_rec IN ar_adjustments%ROWTYPE,
163: p_app_rec IN ar_receivable_applications%ROWTYPE,
164: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type);
165:
166: PROCEDURE update_taxable
496: (p_app_id IN NUMBER)
497: IS
498: BEGIN
499: --
500: -- application on a 11i Mfar transaction with Mfar adjustments
501: --
502: IF g_mode_process = 'R12_11IMFAR' THEN
503:
504: UPDATE ar_receivable_applications
541: -- Note this process is needed when application is getting posted
542: -- R12 -- At the end of nornal process
543: IF g_mode_process IN ('R12','R12_NLB','R12_11ICASH','R12_11IMFAR') THEN
544:
545: UPDATE ar_adjustments
546: SET upgrade_method = 'R12'
547: WHERE adjustment_id = p_adj_id;
548:
549: END IF;
706: PROCEDURE prepare_trx_line_proration
707: (p_gt_id IN VARCHAR2,
708: p_customer_trx_id IN NUMBER,
709: p_pay_adj IN VARCHAR2,
710: p_adj_rec IN ar_adjustments%ROWTYPE,
711: p_app_rec IN ar_receivable_applications%ROWTYPE,
712: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type);
713:
714: /*-------------------------------------------------------------------------+
730: PROCEDURE prepare_group_for_proration
731: (p_gt_id IN VARCHAR2,
732: p_customer_trx_id IN NUMBER,
733: p_pay_adj IN VARCHAR2,
734: p_adj_rec IN ar_adjustments%ROWTYPE,
735: p_app_rec IN ar_receivable_applications%ROWTYPE,
736: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type);
737:
738: /*-------------------------------------------------------------------------+
782: PROCEDURE get_inv_dist
783: (p_pay_adj IN VARCHAR2,
784: p_customer_trx_id IN NUMBER,
785: p_gt_id IN VARCHAR2,
786: p_adj_rec IN ar_adjustments%ROWTYPE,
787: p_app_rec IN ar_receivable_applications%ROWTYPE,
788: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type);
789:
790:
842: (p_customer_trx_id IN NUMBER,
843: p_gt_id IN VARCHAR2,
844: p_pay_adj IN VARCHAR2,
845: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
846: p_adj_rec IN ar_adjustments%ROWTYPE);
847:
848: /*-------------------------------------------------------------------------+
849: | proration_app_dist_trx |
850: +-------------------------------------------------------------------------+
868: PROCEDURE proration_adj_dist_trx
869: (p_gt_id IN VARCHAR2,
870: p_app_level IN VARCHAR2,
871: p_customer_trx_id IN NUMBER,
872: p_adj_rec IN ar_adjustments%ROWTYPE,
873: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type);
874:
875:
876: -------------
3231: PROCEDURE prepare_group_for_proration
3232: (p_gt_id IN VARCHAR2,
3233: p_customer_trx_id IN NUMBER,
3234: p_pay_adj IN VARCHAR2,
3235: p_adj_rec IN ar_adjustments%ROWTYPE,
3236: p_app_rec IN ar_receivable_applications%ROWTYPE,
3237: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type)
3238: IS
3239: l_rows NUMBER;
4053: END;
4054:
4055: PROCEDURE conv_acctd_amt
4056: (p_pay_adj IN VARCHAR2,
4057: p_adj_rec IN ar_adjustments%ROWTYPE,
4058: p_app_rec IN ar_receivable_applications%ROWTYPE,
4059: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type)
4060: IS
4061: l_acctd_amt NUMBER;
4230: -- org context so that it is usable in Down time upgrade
4231: --
4232: PROCEDURE conv_acctd_amt_upg
4233: (p_pay_adj IN VARCHAR2,
4234: p_adj_rec IN ar_adjustments%ROWTYPE,
4235: p_app_rec IN ar_receivable_applications%ROWTYPE,
4236: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type)
4237: IS
4238: l_acctd_amt NUMBER;
4564: PROCEDURE prepare_trx_line_proration
4565: (p_gt_id IN VARCHAR2,
4566: p_customer_trx_id IN NUMBER,
4567: p_pay_adj IN VARCHAR2,
4568: p_adj_rec IN ar_adjustments%ROWTYPE,
4569: p_app_rec IN ar_receivable_applications%ROWTYPE,
4570: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type)
4571: IS
4572: BEGIN
5733: PROCEDURE get_inv_dist
5734: (p_pay_adj IN VARCHAR2,
5735: p_customer_trx_id IN NUMBER,
5736: p_gt_id IN VARCHAR2,
5737: p_adj_rec IN ar_adjustments%ROWTYPE,
5738: p_app_rec IN ar_receivable_applications%ROWTYPE,
5739: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type)
5740: IS
5741:
6636: 'REV','Y', -- BUG 7597090
6637: 'N') -- tax_inc_flag
6638: FROM ra_ar_gt rar,
6639: ar_distributions rrc,
6640: ar_adjustments adj
6641: WHERE rar.gt_id = p_gt_id
6642: AND rar.ref_customer_trx_id = p_customer_trx_id
6643: AND rar.gp_level = 'L'
6644: AND adj.customer_trx_id = p_customer_trx_id
6876: FROM ra_ar_gt
6877: WHERE gt_id = p_gt_id
6878: AND ref_customer_trx_id = p_customer_trx_id
6879: AND gp_level = 'L') g,
6880: ar_adjustments adj,
6881: ra_customer_trx trx
6882: --{Line Charge and Freight boundary
6883: -- Insert freight dist or the charge dist only in the no CHARGES or FREIGHT line exist
6884: -- As by default the boundary on those 2 lines exist only if there are no REV line on the invoice
7107: FROM ra_ar_gt rar,
7108: psa_mf_adj_dist_all rrc,
7109: ra_cust_trx_line_gl_dist ctlgd,
7110: ra_customer_trx_lines ctl,
7111: ar_adjustments adj
7112: WHERE rar.gt_id = p_gt_id
7113: AND rar.ref_customer_trx_id = p_customer_trx_id
7114: AND rar.gp_level = 'L'
7115: AND rar.ref_customer_trx_line_id = ctl.customer_trx_line_id
8426: FROM ar_receivable_applications ar
8427: WHERE ar.applied_customer_trx_id = inv.customer_trx_id
8428: UNION
8429: SELECT 1
8430: FROM ar_adjustments ad
8431: WHERE ad.customer_trx_id = inv.customer_trx_id
8432: );
8433:
8434:
9398: (p_customer_trx_id IN NUMBER,
9399: p_gt_id IN VARCHAR2,
9400: p_pay_adj IN VARCHAR2,
9401: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
9402: p_adj_rec IN ar_adjustments%ROWTYPE)
9403: IS
9404: CURSOR get_sum_per_line_type IS
9405: SELECT /*+INDEX (RA_AR_GT ra_ar_n1)*/
9406: MAX(sum_line_rem_amt),
9459: l_line_boundary_done VARCHAR2(1) := 'N';
9460: l_tax_boundary_done VARCHAR2(1) := 'N';
9461: l_chrg_boundary_done VARCHAR2(1) := 'N';
9462: PROCEDURE line_boundary
9463: (p_adj_rec IN ar_adjustments%ROWTYPE,
9464: p_sum_line_orig_amt IN NUMBER,
9465: p_sum_line_rem_amt IN NUMBER,
9466: p_inv_currency IN VARCHAR2,
9467: p_customer_trx_id IN NUMBER,
9642: END IF;
9643: END line_boundary;
9644:
9645: PROCEDURE tax_boundary
9646: (p_adj_rec IN ar_adjustments%ROWTYPE,
9647: p_sum_line_tax_orig_amt IN NUMBER,
9648: p_sum_line_tax_rem_amt IN NUMBER,
9649: p_inv_currency IN VARCHAR2,
9650: p_customer_trx_id IN NUMBER,
9716: is over revenue line and not freight lines
9717: Keep this procedure for future
9718:
9719: PROCEDURE freight_boundary
9720: (p_adj_rec IN ar_adjustments%ROWTYPE,
9721: p_sum_line_frt_orig_amt IN NUMBER,
9722: p_sum_line_frt_rem_amt IN NUMBER,
9723: p_inv_currency IN VARCHAR2,
9724: p_customer_trx_id IN NUMBER,
10960: p_customer_trx_id IN NUMBER,
10961: p_app_rec IN ar_receivable_applications%ROWTYPE,
10962: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type)
10963: IS
10964: l_adj_rec ar_adjustments%ROWTYPE;
10965: BEGIN
10966: IF PG_DEBUG = 'Y' THEN
10967: localdebug('arp_det_dist_pkg.proration_app_dist_trx()+');
10968: localdebug(' p_gt_id :'||p_gt_id );
11002: PROCEDURE proration_adj_dist_trx
11003: (p_gt_id IN VARCHAR2,
11004: p_app_level IN VARCHAR2,
11005: p_customer_trx_id IN NUMBER,
11006: p_adj_rec IN ar_adjustments%ROWTYPE,
11007: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type)
11008: IS
11009: l_app_rec ar_receivable_applications%ROWTYPE;
11010: BEGIN
11078: p_app_rec IN ar_receivable_applications%ROWTYPE,
11079: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
11080: p_gt_id IN VARCHAR2 DEFAULT NULL)
11081: IS
11082: l_adj_rec ar_adjustments%ROWTYPE;
11083: l_app_rec ar_receivable_applications%ROWTYPE;
11084: l_gt_id VARCHAR2(30);
11085: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
11086: l_msg_data VARCHAR2(2000);
11255: p_app_rec IN ar_receivable_applications%ROWTYPE,
11256: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
11257: p_gt_id IN VARCHAR2 DEFAULT NULL)
11258: IS
11259: l_adj_rec ar_adjustments%ROWTYPE;
11260: l_app_rec ar_receivable_applications%ROWTYPE;
11261: l_gt_id VARCHAR2(30);
11262: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
11263: l_msg_data VARCHAR2(2000);
11455: p_app_rec IN ar_receivable_applications%ROWTYPE,
11456: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
11457: p_gt_id IN VARCHAR2 DEFAULT NULL)
11458: IS
11459: l_adj_rec ar_adjustments%ROWTYPE;
11460: l_app_rec ar_receivable_applications%ROWTYPE;
11461: l_gt_id VARCHAR2(30);
11462: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
11463: l_msg_data VARCHAR2(2000);
11767: (SELECT SUM( NVL(ard.amount_cr,0) - NVL(ard.amount_dr,0) ) amt,
11768: SUM( NVL(ard.acctd_amount_cr,0) - NVL(ard.acctd_amount_dr,0)) acctd_amt,
11769: ard.ref_customer_trx_line_id,
11770: ard.activity_bucket
11771: FROM ar_adjustments adj,
11772: ar_distributions ard,
11773: ra_customer_trx_lines ctl
11774: WHERE ctl.customer_trx_id = p_customer_trx_id
11775: AND ctl.line_type = 'LINE'
12016:
12017: PROCEDURE create_final_split
12018: (p_customer_trx IN ra_customer_trx%ROWTYPE,
12019: p_app_rec IN ar_receivable_applications%ROWTYPE,
12020: p_adj_rec IN ar_adjustments%ROWTYPE,
12021: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type)
12022: IS
12023: BEGIN
12024: IF PG_DEBUG = 'Y' THEN
12081: | p_ae_sys_rec ar system parameter
12082: +-------------------------------------------------------------------------*/
12083: PROCEDURE Trx_level_direct_adjust
12084: (p_customer_trx IN ra_customer_trx%ROWTYPE,
12085: p_adj_rec IN ar_adjustments%ROWTYPE,
12086: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
12087: p_gt_id IN NUMBER DEFAULT NULL)
12088: IS
12089: l_app_rec ar_receivable_applications%ROWTYPE;
12195: p_app_rec => l_app_rec,
12196: p_adj_rec => p_adj_rec,
12197: p_ae_sys_rec => p_ae_sys_rec);
12198:
12199: UPDATE ar_adjustments SET upgrade_method = 'R12'
12200: WHERE adjustment_id = p_adj_rec.adjustment_id;
12201:
12202: IF PG_DEBUG = 'Y' THEN
12203: localdebug('arp_det_dist_pkg.trx_level_direct_adjust()-');
12238: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
12239: p_gt_id IN NUMBER DEFAULT NULL,
12240: p_inv_cm IN VARCHAR2 DEFAULT 'I')
12241: IS
12242: l_adj_rec ar_adjustments%ROWTYPE;
12243: l_app_rec ar_receivable_applications%ROWTYPE;
12244: l_gt_id NUMBER;
12245: l_out_res VARCHAR2(1);
12246: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
12927: | the buckets Rev is <> 0 |
12928: +------------------------------------------------------+
12929: | Created 26-OCT-03 Herve Yu |
12930: +------------------------------------------------------*/
12931: FUNCTION tax_adj_type(p_adj_rec IN ar_adjustments%rowtype,
12932: p_ae_rule_rec IN ae_rule_rec_type,
12933: p_amt_rem IN g_amt_rem_type,
12934: p_app_rec IN ar_receivable_applications%rowtype,
12935: p_source_exec IN VARCHAR2)
13042: | the buckets Rev is <> 0 |
13043: +------------------------------------------------------+
13044: | Created 26-OCT-03 Herve Yu |
13045: +------------------------------------------------------*/
13046: FUNCTION line_adj_type(p_adj_rec IN ar_adjustments%rowtype,
13047: p_ae_rule_rec IN ae_rule_rec_type,
13048: p_amt_rem IN g_amt_rem_type,
13049: p_app_rec IN ar_receivable_applications%rowtype,
13050: p_source_exec IN VARCHAR2)
13126: | the buckets Rev and Freight at header are <> 0 |
13127: +------------------------------------------------------+
13128: | Created 26-OCT-03 Herve Yu |
13129: +------------------------------------------------------*/
13130: FUNCTION frt_adj_type(p_adj_rec IN ar_adjustments%rowtype,
13131: p_ae_rule_rec IN ae_rule_rec_type,
13132: p_amt_rem IN g_amt_rem_type,
13133: p_app_rec IN ar_receivable_applications%rowtype,
13134: p_source_exec IN VARCHAR2)
13223: | the buckets Rev and Freight at header are <> 0 |
13224: +------------------------------------------------------+
13225: | Created 26-OCT-03 Herve Yu |
13226: +------------------------------------------------------*/
13227: FUNCTION chrg_adj_type(p_adj_rec IN ar_adjustments%rowtype,
13228: p_ae_rule_rec IN ae_rule_rec_type,
13229: p_amt_rem IN g_amt_rem_type,
13230: p_app_rec IN ar_receivable_applications%rowtype,
13231: p_source_exec IN VARCHAR2)
13301: | x_chrg_adj codification for charge adjustment |
13302: +-----------------------------------------------------------+
13303: | Created 26-OCT-03 Herve Yu |
13304: +-----------------------------------------------------------*/
13305: PROCEDURE possible_adjust(p_adj_rec IN ar_adjustments%rowtype,
13306: p_ae_rule_rec IN ae_rule_rec_type,
13307: p_customer_trx_id IN NUMBER,
13308: x_return_status OUT NOCOPY VARCHAR2,
13309: x_line_adj OUT NOCOPY VARCHAR2,
13805:
13806:
13807:
13808: PROCEDURE exec_adj_api_if_required
13809: (p_adj_rec IN ar_adjustments%ROWTYPE,
13810: p_app_rec IN ar_receivable_applications%ROWTYPE,
13811: p_ae_rule_rec IN ae_rule_rec_type,
13812: p_cust_inv_rec IN ra_customer_trx%ROWTYPE)
13813: IS
13829:
13830: l_rev_adj_rec AR_Revenue_Adjustment_PVT.Rev_Adj_Rec_Type;
13831: l_adj_id NUMBER;
13832: l_dist_count NUMBER;
13833: l_adj_number ar_adjustments.adjustment_number%TYPE;
13834: l_ra_dist_tbl AR_Revenue_Adjustment_PVT.RA_Dist_Tbl_Type;
13835:
13836: l_return_status VARCHAR2(1);
13837: l_msg_count NUMBER;
13960:
13961: PROCEDURE exec_revrec_if_required
13962: ( p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE,
13963: p_app_rec IN ar_receivable_applications%ROWTYPE,
13964: p_adj_rec IN ar_adjustments%ROWTYPE)
13965: IS
13966: CURSOR c1 IS
13967: SELECT ctl.customer_trx_id
13968: FROM ra_customer_trx_lines ctl
14682: PROCEDURE update_for_mrc_dist
14683: (p_gt_id IN VARCHAR2,
14684: p_customer_trx_id IN NUMBER,
14685: p_app_rec IN ar_receivable_applications%ROWTYPE,
14686: p_adj_rec IN ar_adjustments%ROWTYPE,
14687: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type)
14688: IS
14689: CURSOR c_mrc(p_acctd_adj_app_to IN NUMBER,
14690: p_acctd_app_from IN NUMBER,
15458: SELECT * FROM ra_customer_trx_all
15459: WHERE customer_trx_id = p_trx_id;
15460: l_trx_rec ra_customer_trx_all%ROWTYPE;
15461: l_exec_status VARCHAR2(10) := 'N';
15462: l_adj_rec ar_adjustments%ROWTYPE;
15463: l_app_rec ar_receivable_applications%ROWTYPE;
15464: next_app EXCEPTION;
15465: BEGIN
15466: --
15788: IS
15789: CURSOR c IS
15790: SELECT tt.psa_trx_type_id psa_tt_id,
15791: (SELECT adjustment_id
15792: FROM ar_adjustments adj
15793: WHERE customer_trx_id = trx.customer_trx_id
15794: AND upgrade_method = '11I'
15795: AND rownum = 1) c11iadj,
15796: (SELECT adjustment_id
15793: WHERE customer_trx_id = trx.customer_trx_id
15794: AND upgrade_method = '11I'
15795: AND rownum = 1) c11iadj,
15796: (SELECT adjustment_id
15797: FROM ar_adjustments adj
15798: WHERE customer_trx_id = trx.customer_trx_id
15799: AND upgrade_method = '11IMFAR'
15800: AND rownum = 1) mfadj,
15801: (SELECT app.receivable_application_id
15882: x_out_res OUT NOCOPY VARCHAR2)
15883: IS
15884: CURSOR c11I IS
15885: SELECT 'Y'
15886: FROM ar_adjustments adj
15887: WHERE adj.customer_trx_id = p_trx_id
15888: AND adj.status = 'A'
15889: AND adj.postable = 'Y'
15890: AND adj.upgrade_method IN ('11I','11IMFAR') ;
15936: p_gt_id IN NUMBER,
15937: --Add the p_inv_cm flag
15938: p_inv_cm IN VARCHAR2 DEFAULT 'I')
15939: IS
15940: l_adj_rec ar_adjustments%ROWTYPE;
15941: l_app_rec ar_receivable_applications%ROWTYPE;
15942: BEGIN
15943: IF PG_DEBUG = 'Y' THEN
15944: localdebug('arp_det_dist_pkg.Online_lazy_apply()+');
16663: END set_interface_flag;
16664:
16665: PROCEDURE adjustment_with_interface
16666: (p_customer_trx IN ra_customer_trx%ROWTYPE,
16667: p_adj_rec IN ar_adjustments%ROWTYPE,
16668: p_ae_sys_rec IN arp_acct_main.ae_sys_rec_type,
16669: p_gt_id IN NUMBER,
16670: p_line_flag IN VARCHAR2 DEFAULT 'INTERFACE',
16671: p_tax_flag IN VARCHAR2 DEFAULT 'INTERFACE',
16780: IF p_llca_from_call = 'Y' THEN
16781: final_update_inv_ctl_rem_orig(p_customer_trx => p_customer_trx);
16782: END IF;
16783:
16784: UPDATE ar_adjustments SET upgrade_method = 'R12'
16785: WHERE adjustment_id = p_adj_rec.adjustment_id;
16786:
16787: IF PG_DEBUG = 'Y' THEN
16788: localdebug('arp_det_dist_pkg.adjustment_with_interface()-');
16821: x_return_status IN OUT NOCOPY VARCHAR2,
16822: x_msg_count IN OUT NOCOPY NUMBER,
16823: x_msg_data IN OUT NOCOPY VARCHAR2)
16824: IS
16825: l_adj_rec ar_adjustments%ROWTYPE;
16826: BEGIN
16827: IF PG_DEBUG = 'Y' THEN
16828: localdebug('arp_det_dist_pkg.application_with_interface()+');
16829: localdebug(' p_customer_trx_id :'||p_customer_trx.customer_trx_id);
16975:
16976:
16977: PROCEDURE Reconciliation
16978: (p_app_rec IN ar_receivable_applications%ROWTYPE,
16979: p_adj_rec IN ar_adjustments%ROWTYPE,
16980: p_activity_type IN VARCHAR2,
16981: p_gt_id IN VARCHAR2 DEFAULT NULL)
16982: IS
16983: CURSOR cu1(p_gt_id IN NUMBER) IS