1 PACKAGE BODY AP_PREPAY_TRAK_REP_TURK_PKG AS
2 -- $Header: APPPTRPB.pls 120.2.12000000.1 2007/10/24 18:07:08 sgudupat noship $
3 -- ****************************************************************************************
4 -- Copyright (c) 2000 Oracle Solution Services (India) Product Development
5 -- All rights reserved
6 -- ****************************************************************************************
7 --
8 -- PROGRAM NAME
9 -- APPPTRPB.pls
10 --
11 -- DESCRIPTION
12 -- This script creates the package body of APPPTRPB.pls.
13 -- This package is used to generate Prepayment Tracking Report for Turkey.
14 --
15 -- USAGE
16 -- To install How to Install
17 -- To execute How to Execute
18 --
19 -- DEPENDENCIES
20 -- None.
21 --
22 --
23 -- LAST UPDATE DATE 26-JAN-2007
24 -- Date the program has been modified for the last time
25 --
26 -- HISTORY
27 -- =======
28 --
29 -- VERSION DATE AUTHOR(S) DESCRIPTION
30 -- ------- ----------- --------------- ------------------------------------
31 -- 1.0 26-JAN-2007 Pgollu M Creation
32 --
33 --****************************************************************************************
34 FUNCTION beforeReport RETURN BOOLEAN IS
35 BEGIN
36 gd_date_from := TO_DATE(P_INVOICE_FROM,'YYYY/MM/DD HH24:MI:SS');
37 gd_date_to := TO_DATE(P_INVOICE_TO,'YYYY/MM/DD HH24:MI:SS');
38 IF P_INVOICE_FROM IS NOT NULL AND P_INVOICE_TO IS NOT NULL THEN
39 c_lex_invoice_date :=' AND ai.invoice_date
40 BETWEEN TO_DATE(:P_INVOICE_FROM,''YYYY/MM/DD HH24:MI:SS'')
41 AND TO_DATE(:P_INVOICE_TO,''YYYY/MM/DD HH24:MI:SS'')';
42
43 --c_lex_invoice_date :=' AND ai.gl_date BETWEEN'||''''||gd_date_from||''''||' AND '||''''||gd_date_to||'''';
44 ELSE
45 c_lex_invoice_date := ' AND 1 = 1';
46 END IF;
47
48 IF P_PREPAY_STATUS IS NULL THEN
49 c_lex_prepay_status := 'AND 1=1';
50 ELSE
51 c_lex_prepay_status := 'AND ALC.lookup_code=:P_PREPAY_STATUS';
52 END IF;
53
54 IF P_SUPPLIER_FROM IS NOT NULL AND P_SUPPLIER_TO IS NOT NULL THEN
55 IF UPPER(P_SUPPLIER_FROM) <= UPPER(P_SUPPLIER_TO) THEN
56 c_lex_supplier := ' AND UPPER(POV.vendor_name) BETWEEN UPPER(:P_SUPPLIER_FROM) AND UPPER(:P_SUPPLIER_TO)';
57 ELSE
58 c_lex_supplier := ' AND UPPER(POV.vendor_name) BETWEEN UPPER(:P_SUPPLIER_TO) AND UPPER(:P_SUPPLIER_FROM)';
59 END IF;
60 ELSE
61 c_lex_supplier := ' AND 1 = 1';
62 END IF;
63
64 IF P_CURRENCY = 'ANY' THEN
65 c_lex_currency := ' AND 1 = 1';
66 ELSE
67 --c_lex_currency := ' AND AI.INVOICE_CURRENCY_CODE = '''||P_CURRENCY||'''';
68 c_lex_currency := ' AND AI.INVOICE_CURRENCY_CODE = :P_CURRENCY';
69 END IF;
70
71 IF P_VENDOR_TYPE IS NOT NULL THEN
72 --c_lex_vendor_type := ' AND POV.VENDOR_TYPE_LOOKUP_CODE = '''||P_VENDOR_TYPE||'''';
73 c_lex_vendor_type := ' AND POV.VENDOR_TYPE_LOOKUP_CODE = :P_VENDOR_TYPE';
74 ELSE
75 c_lex_vendor_type := ' AND 1 = 1';
76 END IF;
77
78 IF P_ORG_ID IS NOT NULL THEN
79 gc_org_where := ' AND ai.org_id = :P_ORG_ID ';
80 ELSE
81 gc_org_where := ' AND 1 = 1';
82 END IF;
83
84 RETURN TRUE;
85 END beforeReport;
86
87 FUNCTION date_close(prepay_inv_num IN VARCHAR2
88 ,prepay_line_number_param IN NUMBER
89 ,prepay_amount_remaining IN NUMBER) RETURN DATE IS
90 lc_closing_date DATE;
91 BEGIN
92 IF prepay_amount_remaining =0 THEN
93 SELECT MAX(AVPFPV.accounting_date)
94 INTO lc_closing_date
95 FROM ap_view_prepays_fr_prepay_v AVPFPV
96 WHERE AVPFPV.prepay_id= prepay_inv_num
97 AND AVPFPV.prepay_line_number= prepay_line_number_param ;
98 ELSIF prepay_amount_remaining IS NULL OR prepay_amount_remaining>0 THEN
99 RETURN(NULL);
100 END IF;
101 RETURN(lc_closing_date);
102 END date_close;
103
104 END AP_PREPAY_TRAK_REP_TURK_PKG;