[Home] [Help]
13: ( p_api_version IN NUMBER,
14: p_init_msg_list IN VARCHAR2,
15: p_commit IN VARCHAR2,
16: p_validation_level IN NUMBER,
17: p_payrun_id IN cn_payruns.payrun_id%TYPE,
18: x_return_status OUT NOCOPY VARCHAR2,
19: x_msg_count OUT NOCOPY NUMBER,
20: x_msg_data OUT NOCOPY VARCHAR2,
21: x_status OUT NOCOPY VARCHAR2,
26: l_api_version CONSTANT NUMBER := 1.0;
27:
28: CURSOR get_old_record IS
29: SELECT status, payrun_id,org_id,object_version_number
30: FROM cn_payruns
31: WHERE payrun_id = p_payrun_id;
32: l_old_record get_old_record%ROWTYPE;
33:
34: CURSOR get_worksheets(p_org_id cn_payruns.org_id%TYPE) IS
30: FROM cn_payruns
31: WHERE payrun_id = p_payrun_id;
32: l_old_record get_old_record%ROWTYPE;
33:
34: CURSOR get_worksheets(p_org_id cn_payruns.org_id%TYPE) IS
35: SELECT payment_worksheet_id,object_version_number
36: FROM cn_payment_worksheets
37: WHERE payrun_id = p_payrun_id
38: AND quota_id IS NULL
230: SELECT name,
231: org_id,
232: pay_period_id,
233: pay_date
234: FROM cn_payruns
235: WHERE payrun_id = p_payrun_id ;
236:
237: l_payrun_rec get_payruns%ROWTYPE;
238:
246: rs.status status
247: FROM jtf_rs_salesreps rs,
248: jtf_rs_resource_extns rre,
249: per_assignments_f p,
250: cn_payruns ps
251: WHERE rs.salesrep_id = p_salesrep_id
252: AND rs.org_id = p_org_id
253: AND rs.resource_id = rre.resource_id
254: AND rre.category = 'EMPLOYEE'
270: piv.input_value_id element_input_id ,
271: piv.uom uom, piv.name element_input_name
272: FROM cn_pay_element_inputs pei,
273: cn_quota_pay_elements qpe,
274: cn_payruns p,
275: cn_objects tab,
276: cn_objects col,
277: pay_element_types_f pet,
278: pay_input_values_f piv
305: FROM
306: pay_input_values_f piv,
307: pay_element_types_f pet,
308: cn_quota_pay_elements qpe,
309: cn_payruns p
310: WHERE qpe.pay_element_type_id = p_element_type_id
311: AND qpe.quota_id = decode(p_incentive_type_code,
312: 'PMTPLN_REC' , -1001,
313: p_quota_id)
343:
344: --Modified by Sundar Venkat for bug fix 2660893
345: -- AC 04/09/03 2892822 : need to join with p_Incentive_type_code
346: l_where VARCHAR2(2000) :=
347: ' CN_PAYRUNS.PAYRUN_ID = :B1 '
348: ||' AND CN_SALESREPS.SALESREP_ID = :B2 '
349: ||' AND CN_PAYRUNS.ORG_ID = CN_SALESREPS.ORG_ID '
350: ||' AND CN_PAYMENT_TRANSACTIONS.QUOTA_ID = :B3'
351: ||' AND CN_PAYMENT_TRANSACTIONS.PAYRUN_ID = CN_PAYRUNS.PAYRUN_ID'
345: -- AC 04/09/03 2892822 : need to join with p_Incentive_type_code
346: l_where VARCHAR2(2000) :=
347: ' CN_PAYRUNS.PAYRUN_ID = :B1 '
348: ||' AND CN_SALESREPS.SALESREP_ID = :B2 '
349: ||' AND CN_PAYRUNS.ORG_ID = CN_SALESREPS.ORG_ID '
350: ||' AND CN_PAYMENT_TRANSACTIONS.QUOTA_ID = :B3'
351: ||' AND CN_PAYMENT_TRANSACTIONS.PAYRUN_ID = CN_PAYRUNS.PAYRUN_ID'
352: ||' AND CN_PAYMENT_TRANSACTIONS.INCENTIVE_TYPE_CODE = :B4 '
353: ||' AND CN_PAYMENT_TRANSACTIONS.CREDITED_SALESREP_ID = CN_SALESREPS.SALESREP_ID'
347: ' CN_PAYRUNS.PAYRUN_ID = :B1 '
348: ||' AND CN_SALESREPS.SALESREP_ID = :B2 '
349: ||' AND CN_PAYRUNS.ORG_ID = CN_SALESREPS.ORG_ID '
350: ||' AND CN_PAYMENT_TRANSACTIONS.QUOTA_ID = :B3'
351: ||' AND CN_PAYMENT_TRANSACTIONS.PAYRUN_ID = CN_PAYRUNS.PAYRUN_ID'
352: ||' AND CN_PAYMENT_TRANSACTIONS.INCENTIVE_TYPE_CODE = :B4 '
353: ||' AND CN_PAYMENT_TRANSACTIONS.CREDITED_SALESREP_ID = CN_SALESREPS.SALESREP_ID'
354: ||' AND CN_PAYMENT_TRANSACTIONS.PAY_ELEMENT_TYPE_ID IS NOT NULL';
355:
352: ||' AND CN_PAYMENT_TRANSACTIONS.INCENTIVE_TYPE_CODE = :B4 '
353: ||' AND CN_PAYMENT_TRANSACTIONS.CREDITED_SALESREP_ID = CN_SALESREPS.SALESREP_ID'
354: ||' AND CN_PAYMENT_TRANSACTIONS.PAY_ELEMENT_TYPE_ID IS NOT NULL';
355:
356: l_from VARCHAR2(2000) := ' CN_PAYRUNS, CN_SALESREPS, CN_PAYMENT_TRANSACTIONS ';
357:
358: l_select VARCHAR2(32000) ;
359:
360: -- Total Input Values Defined
390: SELECT glsob.set_of_books_id set_of_books_id,
391: glsob.currency_code currency_code
392: FROM gl_sets_of_books glsob,
393: cn_repositories cnr,
394: cn_payruns cnp
395: WHERE cnr.set_of_books_id = glsob.set_of_books_id
396: AND cnr.org_id = cnp.org_id
397: AND cnp.payrun_id = p_payrun_id;
398:
438: open get_payruns;
439: fetch get_payruns into l_payrun_rec;
440: close get_payruns;
441:
442: --Bug 3314913 by jjhuang on 12/15/03. In length(cn_payruns.name)=80, but the length for integration only needs 30.
443: l_payrun_rec.name := substr(l_payrun_rec.name, 1, 30);
444:
445: --
446: -- get assignment , assignment_id, bussiness group id, status
481: || ' C_'||l_count|| ',';
482: --Commented by Sundar Venkat for bug fix 2660893
483: --IF l_flag_payment_transactions = 'N' THEN
484: -- l_from := l_from || ', ' || tab_columns.table_name ;
485: -- l_where := l_where || ' AND CN_PAYMENT_TRANSACTIONS.PAYRUN_ID = CN_PAYRUNS.PAYRUN_ID ' ;
486: -- l_flag_payment_transactions := 'Y';
487: --END IF ;
488:
489: ELSIF tab_columns.table_name iS NOT NULL THEN
493: || ' C_'||l_count|| ',';
494: --Commented by Sundar Venkat for bug fix 2660893
495: -- IF l_flag_payment_transactions = 'N' AND tab_columns.table_name = 'CN_PAYMENT_TRANSACTIONS' THEN
496: -- l_from := l_from || ', ' || tab_columns.table_name ;
497: -- l_where := l_where || ' AND CN_PAYMENT_TRANSACTIONS.PAYRUN_ID= CN_PAYRUNS.PAYRUN_ID ' ;
498: -- l_flag_payment_transactions := 'Y';
499: -- END IF ;
500:
501:
824: l_num_pe_mapping NUMBER;
825: l_pe_name varchar2(80);
826:
827:
828: CURSOR get_quotas(p_payrun cn_payruns.payrun_id%TYPE) IS
829: select distinct w.quota_id,q.name
830: from cn_payment_worksheets w,
831: cn_quotas q,cn_salesreps cns
832: where payrun_id=p_payrun
991: --
992: -- ===========================================================================
993: FUNCTION validate_payrun_status
994: (
995: p_status IN cn_payruns.status%TYPE,
996: p_loading_status IN VARCHAR2,
997: x_loading_status OUT NOCOPY VARCHAR2
998: ) RETURN VARCHAR2 IS
999:
1050: --
1051: -- ===========================================================================
1052: FUNCTION validate_name_unique
1053: (
1054: p_name IN cn_payruns.name%TYPE,
1055: p_org_id IN cn_payruns.org_id%TYPE,
1056: p_loading_status IN VARCHAR2,
1057: x_loading_status OUT NOCOPY VARCHAR2
1058: ) RETURN VARCHAR2 IS
1051: -- ===========================================================================
1052: FUNCTION validate_name_unique
1053: (
1054: p_name IN cn_payruns.name%TYPE,
1055: p_org_id IN cn_payruns.org_id%TYPE,
1056: p_loading_status IN VARCHAR2,
1057: x_loading_status OUT NOCOPY VARCHAR2
1058: ) RETURN VARCHAR2 IS
1059:
1060: l_api_name CONSTANT VARCHAR2(30) := 'Validate_name_unique';
1061:
1062: CURSOR get_count IS
1063: SELECT COUNT(1)
1064: FROM cn_payruns
1065: WHERE name = p_name
1066: AND org_id=p_org_id;
1067:
1068: l_count NUMBER;
1124: -- ===========================================================================
1125:
1126: FUNCTION validate_pay_group
1127: (
1128: p_pay_group_id IN cn_payruns.pay_group_id%TYPE,
1129: p_loading_status IN VARCHAR2,
1130: x_loading_status OUT NOCOPY VARCHAR2
1131: ) RETURN VARCHAR2 IS
1132:
1195: -- ===========================================================================
1196:
1197: FUNCTION validate_pay_period
1198: (
1199: p_pay_group_id IN cn_payruns.pay_group_id%TYPE,
1200: p_pay_period_id IN cn_payruns.pay_period_id%TYPE,
1201: p_loading_status IN VARCHAR2,
1202: x_loading_status OUT NOCOPY VARCHAR2
1203: ) RETURN VARCHAR2 IS
1196:
1197: FUNCTION validate_pay_period
1198: (
1199: p_pay_group_id IN cn_payruns.pay_group_id%TYPE,
1200: p_pay_period_id IN cn_payruns.pay_period_id%TYPE,
1201: p_loading_status IN VARCHAR2,
1202: x_loading_status OUT NOCOPY VARCHAR2
1203: ) RETURN VARCHAR2 IS
1204:
1272: -- ===========================================================================
1273:
1274: FUNCTION check_unpaid_payrun
1275: (
1276: p_pay_group_id IN cn_payruns.pay_group_id%TYPE,
1277: p_loading_status IN VARCHAR2,
1278: x_loading_status OUT NOCOPY VARCHAR2
1279: ) RETURN VARCHAR2 IS
1280:
1281: l_api_name CONSTANT VARCHAR2(30) := 'Check_unpaid_payrun';
1282:
1283: CURSOR get_count IS
1284: SELECT COUNT(1)
1285: FROM cn_payruns
1286: WHERE pay_group_id = p_pay_group_id
1287: AND status <> 'PAID';
1288:
1289: l_count NUMBER;
1343: -- ===========================================================================
1344:
1345: FUNCTION chk_last_paid_prd
1346: (
1347: p_pay_group_id IN cn_payruns.pay_group_id%TYPE,
1348: p_org_id IN cn_payruns.org_id%TYPE,
1349: p_pay_period_id IN cn_payruns.pay_period_id%TYPE,
1350: p_loading_status IN VARCHAR2,
1351: x_loading_status OUT NOCOPY VARCHAR2
1344:
1345: FUNCTION chk_last_paid_prd
1346: (
1347: p_pay_group_id IN cn_payruns.pay_group_id%TYPE,
1348: p_org_id IN cn_payruns.org_id%TYPE,
1349: p_pay_period_id IN cn_payruns.pay_period_id%TYPE,
1350: p_loading_status IN VARCHAR2,
1351: x_loading_status OUT NOCOPY VARCHAR2
1352: ) RETURN VARCHAR2 IS
1345: FUNCTION chk_last_paid_prd
1346: (
1347: p_pay_group_id IN cn_payruns.pay_group_id%TYPE,
1348: p_org_id IN cn_payruns.org_id%TYPE,
1349: p_pay_period_id IN cn_payruns.pay_period_id%TYPE,
1350: p_loading_status IN VARCHAR2,
1351: x_loading_status OUT NOCOPY VARCHAR2
1352: ) RETURN VARCHAR2 IS
1353:
1354: l_api_name CONSTANT VARCHAR2(30) := 'Chk_last_paid_prd';
1355:
1356: CURSOR get_last_pay_period IS
1357: SELECT pay_period_id
1358: FROM cn_payruns
1359: WHERE pay_group_id = p_pay_group_id
1360: ORDER BY payrun_id desc ;
1361:
1362: CURSOR get_period_range (p_period_id IN cn_period_statuses.period_id%TYPE) IS
1367:
1368: l_get_period_range_rec get_period_range%ROWTYPE;
1369:
1370:
1371: l_pay_period_id cn_payruns.pay_period_id%TYPE;
1372: l_last_start_date cn_period_statuses.start_date%TYPE;
1373: l_cur_start_date cn_period_statuses.start_date%TYPE;
1374:
1375: BEGIN
1442: -- ===========================================================================
1443:
1444: FUNCTION populate_ap_interface
1445: (
1446: p_payrun_id IN cn_payruns.payrun_id%TYPE,
1447: p_loading_status IN VARCHAR2,
1448: x_loading_status OUT NOCOPY VARCHAR2
1449: ) RETURN VARCHAR2 IS
1450:
1515: AND nvl(hold_flag,'N') = 'N';
1516:
1517: CURSOR get_pay_date IS
1518: SELECT pay_date,org_id
1519: FROM cn_payruns
1520: WHERE payrun_id = p_payrun_id;
1521: l_pay_date DATE;
1522:
1523: CURSOR get_functional_currency IS
1523: CURSOR get_functional_currency IS
1524: SELECT currency_code
1525: FROM gl_sets_of_books glsob,
1526: cn_repositories cnr,
1527: cn_payruns cnp
1528: WHERE cnr.set_of_books_id = glsob.set_of_books_id
1529: AND cnr.org_id = cnp.org_id
1530: AND cnp.payrun_id= p_payrun_id;
1531: l_functional_currency gl_sets_of_books.currency_code%TYPE;
1680: -- ===========================================================================
1681:
1682: FUNCTION populate_ccids
1683: (
1684: p_payrun_id IN cn_payruns.payrun_id%TYPE,
1685: p_salesrep_id IN cn_payment_worksheets.salesrep_id%TYPE,
1686: --p_start_date IN DATE,
1687: --p_end_date IN DATE,
1688: -- Bug 3866089 (the same as 11.5.8 bug 3841926, 11.5.10 3866116) by jjhuang on 11/1/04
1695:
1696: CURSOR get_vendors IS
1697: SELECT cns.salesrep_id
1698: FROM cn_salesreps cns,
1699: cn_payruns cnr
1700: WHERE
1701: cns.type = 'SUPPLIER_CONTACT'
1702: AND cns.salesrep_id = p_salesrep_id
1703: AND cns.org_id =cnr.org_id
1725: x_loading_status := p_loading_status ;
1726: --chaged
1727: SELECT Nvl(payables_flag, 'N')
1728: INTO l_payables_flag
1729: FROM cn_repositories cr,cn_payruns cp
1730: where cp.payrun_id = p_payrun_id
1731: and cp.org_id=cr.org_id;
1732:
1733: IF l_payables_flag = 'Y' THEN
2017: x_loading_status := 'CN_PAY_BY_TRANSACTION_PROFILE';
2018: RAISE FND_API.G_EXC_ERROR;
2019: END IF;
2020:
2021: cn_payruns_pkg.insert_record(
2022: x_payrun_id => p_payrun_rec.payrun_id
2023: ,x_name => rtrim(ltrim(p_payrun_rec.name))
2024: ,x_pay_period_id => p_payrun_rec.pay_period_id
2025: ,x_incentive_type_code=> p_payrun_rec.incentive_type_code
2048: x_return_status => x_return_status,
2049: x_msg_count => x_msg_count,
2050: x_msg_data => x_msg_data,
2051: p_source_object_id => p_payrun_rec.payrun_id,
2052: p_source_object_code => 'CN_PAYRUNS',
2053: p_notes => l_note_msg,
2054: p_notes_detail => l_note_msg,
2055: p_note_type => 'CN_SYSGEN', -- for system generated
2056: x_jtf_note_id => l_note_id -- returned
2125: p_validation_level IN NUMBER,
2126: x_return_status OUT NOCOPY VARCHAR2,
2127: x_msg_count OUT NOCOPY NUMBER,
2128: x_msg_data OUT NOCOPY VARCHAR2,
2129: p_payrun_id IN cn_payruns.payrun_id%TYPE,
2130: p_x_obj_ver_number IN OUT NOCOPY cn_payruns.object_version_number%TYPE,
2131: p_action IN VARCHAR2,
2132: x_status OUT NOCOPY VARCHAR2,
2133: x_loading_status OUT NOCOPY VARCHAR2
2126: x_return_status OUT NOCOPY VARCHAR2,
2127: x_msg_count OUT NOCOPY NUMBER,
2128: x_msg_data OUT NOCOPY VARCHAR2,
2129: p_payrun_id IN cn_payruns.payrun_id%TYPE,
2130: p_x_obj_ver_number IN OUT NOCOPY cn_payruns.object_version_number%TYPE,
2131: p_action IN VARCHAR2,
2132: x_status OUT NOCOPY VARCHAR2,
2133: x_loading_status OUT NOCOPY VARCHAR2
2134: )
2142: G_CREATION_DATE DATE := sysdate;
2143: G_CREATED_BY NUMBER := fnd_global.user_id;
2144: G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
2145:
2146: l_status cn_payruns.status%TYPE;
2147: l_status_meaning cn_payruns.status%TYPE;
2148: l_payrun_id NUMBER;
2149: l_note_msg VARCHAR2(240);
2150: l_note_id NUMBER;
2143: G_CREATED_BY NUMBER := fnd_global.user_id;
2144: G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
2145:
2146: l_status cn_payruns.status%TYPE;
2147: l_status_meaning cn_payruns.status%TYPE;
2148: l_payrun_id NUMBER;
2149: l_note_msg VARCHAR2(240);
2150: l_note_id NUMBER;
2151:
2153:
2154:
2155: CURSOR get_old_record IS
2156: SELECT status, payrun_id,name,object_version_number,
2157: cn_api.get_lkup_meaning(cn_payruns.status,'PAYRUN_STATUS') statusmeaning
2158: FROM cn_payruns
2159: WHERE payrun_id = p_payrun_id;
2160: l_old_record get_old_record%ROWTYPE;
2161:
2154:
2155: CURSOR get_old_record IS
2156: SELECT status, payrun_id,name,object_version_number,
2157: cn_api.get_lkup_meaning(cn_payruns.status,'PAYRUN_STATUS') statusmeaning
2158: FROM cn_payruns
2159: WHERE payrun_id = p_payrun_id;
2160: l_old_record get_old_record%ROWTYPE;
2161:
2162: CURSOR get_wksht_csr IS
2309: x_return_status => x_return_status,
2310: x_msg_count => x_msg_count,
2311: x_msg_data => x_msg_data,
2312: p_source_object_id => p_payrun_id,
2313: p_source_object_code => 'CN_PAYRUNS',
2314: p_notes => l_note_msg,
2315: p_notes_detail => l_note_msg,
2316: p_note_type => 'CN_SYSGEN', -- for system generated
2317: x_jtf_note_id => l_note_id -- returned
2379: p_validation_level IN NUMBER,
2380: x_return_status OUT NOCOPY VARCHAR2,
2381: x_msg_count OUT NOCOPY NUMBER,
2382: x_msg_data OUT NOCOPY VARCHAR2,
2383: p_payrun_id IN cn_payruns.payrun_id%TYPE,
2384: p_validation_only IN VARCHAR2,
2385: x_status OUT NOCOPY VARCHAR2,
2386: x_loading_status OUT NOCOPY VARCHAR2
2387: ) IS
2392:
2393:
2394: CURSOR get_old_record IS
2395: SELECT status, payrun_id,name,org_id
2396: FROM cn_payruns
2397: WHERE payrun_id = p_payrun_id;
2398: l_old_record get_old_record%ROWTYPE;
2399:
2400: CURSOR get_conc_request(l_pay_run_name varchar2) IS
2550:
2551: END LOOP;
2552:
2553: IF p_validation_only <> 'Y' THEN
2554: cn_payruns_pkg.delete_record
2555: (x_payrun_id => p_payrun_id) ;
2556:
2557: x_loading_status := 'CN_DELETED';
2558:
2636: (p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2637: x_return_status OUT NOCOPY VARCHAR2,
2638: x_msg_count OUT NOCOPY NUMBER,
2639: x_msg_data OUT NOCOPY VARCHAR2,
2640: p_payrun_id IN cn_payruns.payrun_id%TYPE )
2641: IS
2642:
2643: l_api_name CONSTANT VARCHAR2(30) := 'Pay_Payrun_Approve_Wksht';
2644: l_api_version CONSTANT NUMBER := 1.0;
2792: p_validation_level IN NUMBER,
2793: x_return_status OUT NOCOPY VARCHAR2,
2794: x_msg_count OUT NOCOPY NUMBER,
2795: x_msg_data OUT NOCOPY VARCHAR2,
2796: p_payrun_id IN cn_payruns.payrun_id%TYPE,
2797: p_x_obj_ver_number IN OUT NOCOPY cn_payruns.object_version_number%TYPE,
2798: x_status OUT NOCOPY VARCHAR2,
2799: x_loading_status OUT NOCOPY VARCHAR2
2800: ) IS
2793: x_return_status OUT NOCOPY VARCHAR2,
2794: x_msg_count OUT NOCOPY NUMBER,
2795: x_msg_data OUT NOCOPY VARCHAR2,
2796: p_payrun_id IN cn_payruns.payrun_id%TYPE,
2797: p_x_obj_ver_number IN OUT NOCOPY cn_payruns.object_version_number%TYPE,
2798: x_status OUT NOCOPY VARCHAR2,
2799: x_loading_status OUT NOCOPY VARCHAR2
2800: ) IS
2801:
2837: l_srp_prd_rec cn_srp_periods_pvt.delta_srp_period_rec_type
2838: := cn_srp_periods_pvt.g_miss_delta_srp_period_rec;
2839: l_note_msg VARCHAR2(240);
2840: l_note_id NUMBER;
2841: l_status_meaning cn_payruns.status%TYPE;
2842:
2843: CURSOR get_payrun IS
2844: SELECT pay_period_id, pay_date, accounting_period_id, org_id
2845: ,name, cn_api.get_lkup_meaning(cn_payruns.status,'PAYRUN_STATUS') statusmeaning
2841: l_status_meaning cn_payruns.status%TYPE;
2842:
2843: CURSOR get_payrun IS
2844: SELECT pay_period_id, pay_date, accounting_period_id, org_id
2845: ,name, cn_api.get_lkup_meaning(cn_payruns.status,'PAYRUN_STATUS') statusmeaning
2846: FROM cn_payruns
2847: WHERE payrun_id = p_payrun_id;
2848:
2849: l_payrun_rec get_payrun%ROWTYPE;
2842:
2843: CURSOR get_payrun IS
2844: SELECT pay_period_id, pay_date, accounting_period_id, org_id
2845: ,name, cn_api.get_lkup_meaning(cn_payruns.status,'PAYRUN_STATUS') statusmeaning
2846: FROM cn_payruns
2847: WHERE payrun_id = p_payrun_id;
2848:
2849: l_payrun_rec get_payrun%ROWTYPE;
2850:
2847: WHERE payrun_id = p_payrun_id;
2848:
2849: l_payrun_rec get_payrun%ROWTYPE;
2850:
2851: CURSOR get_salesreps_in_payrun(p_org_id cn_payruns.org_id%TYPE) IS
2852: SELECT distinct salesrep_id
2853: FROM cn_payment_worksheets
2854: WHERE payrun_id = p_payrun_id
2855: --R12
2854: WHERE payrun_id = p_payrun_id
2855: --R12
2856: AND org_id = p_org_id;
2857:
2858: CURSOR get_worksheet_data_for_pe (p_salesrep_id NUMBER, p_org_id cn_payruns.org_id%TYPE)IS
2859: SELECT salesrep_id, quota_id, credit_type_id,
2860: pmt_amount_calc, pmt_amount_adj, pmt_amount_adj_rec,
2861: pmt_amount_adj_nrec, pmt_amount_recovery
2862: FROM cn_payment_worksheets
2868:
2869: CURSOR get_srp_period(p_salesrep_id NUMBER,
2870: p_period_id NUMBER,
2871: p_quota_id NUMBER,
2872: p_org_id cn_payruns.org_id%TYPE) IS
2873: SELECT srp_period_id
2874: FROM cn_srp_periods
2875: WHERE salesrep_id = p_salesrep_id
2876: AND credit_type_id = l_credit_type_id
2882: AND org_id = p_org_id
2883: AND ROWNUM < 2 ; -- Bug 2819874
2884:
2885: -- for payroll integration
2886: CURSOR get_apps(p_org_id cn_payruns.org_id%TYPE) IS
2887: SELECT payables_flag, payroll_flag, payables_ccid_level
2888: FROM cn_repositories
2889: --R12
2890: WHERE org_id = p_org_id;
2893:
2894: -- for wkshts with null quota ID - get from transactions
2895: CURSOR get_control_pmt(p_salesrep_id NUMBER,
2896: p_quota_id NUMBER,
2897: p_org_id cn_payruns.org_id%TYPE) IS
2898: SELECT nvl(sum(nvl(payment_amount,0) - nvl(amount,0)),0) control_payment
2899: FROM cn_payment_transactions
2900: WHERE payrun_id = p_payrun_id
2901: AND credited_salesrep_id = p_salesrep_id
2922: -- Bug 2795606 : use amount not pmt_amt since get_cp will handle adj amt
2923:
2924: CURSOR get_man_pay_adj(p_salesrep_id NUMBER,
2925: p_quota_id NUMBER,
2926: p_org_id cn_payruns.org_id%TYPE) IS
2927: SELECT nvl(sum(nvl(amount,0)),0) man_pay_adj, recoverable_flag
2928: FROM cn_payment_transactions
2929: WHERE payrun_id = p_payrun_id
2930: AND credited_salesrep_id = p_salesrep_id
2934: GROUP BY recoverable_flag;
2935:
2936: CURSOR get_payment_details(p_salesrep_id NUMBER,
2937: p_quota_id NUMBER,
2938: p_org_id cn_payruns.org_id%TYPE) IS
2939: SELECT nvl(pmt_amount_calc,0),
2940: nvl(pmt_amount_adj_rec,0),
2941: nvl(pmt_amount_adj_nrec,0),
2942: -nvl(pmt_amount_recovery,0)
2949: (p_quota_id IS NULL AND quota_id IS NULL));
2950:
2951: CURSOR get_waive_rec(p_salesrep_id NUMBER,
2952: p_quota_id NUMBER,
2953: p_org_id cn_payruns.org_id%TYPE) IS
2954: SELECT -nvl(sum(nvl(payment_amount,0)),0)
2955: FROM cn_payment_transactions
2956: WHERE payrun_id = p_payrun_id
2957: AND credited_salesrep_id = p_salesrep_id
2967:
2968: -- get carry over srp_periods record
2969: CURSOR carry_over_srp_period(c_salesrep_id NUMBER,
2970: c_period_id NUMBER,
2971: p_org_id cn_payruns.org_id%TYPE) IS
2972: SELECT sprd.srp_period_id
2973: FROM cn_srp_periods sprd
2974: WHERE
2975: sprd.salesrep_id = c_salesrep_id
2981:
2982: -- Get sync_accum records. Bug 3151860
2983: CURSOR sync_accum(c_salesrep_id NUMBER,
2984: c_period_id NUMBER,
2985: p_org_id cn_payruns.org_id%TYPE) IS
2986: SELECT DISTINCT sprd.role_id
2987: FROM cn_srp_periods sprd
2988: WHERE
2989: sprd.salesrep_id = c_salesrep_id
3410: x_return_status => x_return_status,
3411: x_msg_count => x_msg_count,
3412: x_msg_data => x_msg_data,
3413: p_source_object_id => p_payrun_id,
3414: p_source_object_code => 'CN_PAYRUNS',
3415: p_notes => l_note_msg,
3416: p_notes_detail => l_note_msg,
3417: p_note_type => 'CN_SYSGEN', -- for system generated
3418: x_jtf_note_id => l_note_id -- returned
3480: --============================================================================
3481: PROCEDURE delete_payrun_conc
3482: ( errbuf OUT NOCOPY VARCHAR2,
3483: retcode OUT NOCOPY NUMBER ,
3484: p_name cn_payruns.name%TYPE,
3485: --R12
3486: p_org_name hr_operating_units.name%TYPE) IS
3487:
3488: l_proc_audit_id NUMBER;
3496:
3497:
3498: Cursor get_payrun_id_curs IS
3499: select cp.payrun_id , cp.org_id,cp.OBJECT_VERSION_NUMBER
3500: from cn_payruns cp,
3501: --R12
3502: hr_operating_units hou
3503: where cp.name = p_name
3504: and cp.org_id = hou.organization_id
3504: and cp.org_id = hou.organization_id
3505: and hou.name = p_org_name;
3506:
3507: --R12
3508: l_org_id cn_payruns.org_id%TYPE;
3509: l_obj cn_payruns.object_version_number%type;
3510:
3511: BEGIN
3512:
3505: and hou.name = p_org_name;
3506:
3507: --R12
3508: l_org_id cn_payruns.org_id%TYPE;
3509: l_obj cn_payruns.object_version_number%type;
3510:
3511: BEGIN
3512:
3513: retcode := 0;