Interface to AP(Account Payables)

CREATE OR REPLACE PACKAGE BODY APPS.cmg_incen_epay_to_ap_pck
/*
||  CMG INCENTIVE EPAYMENTS TO AP

AS
   -- Global Variables for the package body
   error_msg        VARCHAR2 (1000) := NULL;
   output_message   VARCHAR2 (1000) := NULL;

   PROCEDURE cmg_incen_epay_to_ap_main_prc (
      errbuf    IN   VARCHAR2,
      retcode   IN   VARCHAR2,
      P_DATE IN varchar2
   )
   AS
      /* RUN FOR A SPECIFIC PARTY_NUMBER WHICH IS DERIVED FROM INCENTIVES ALSO CALLED AS RDA_RDP_ACCOUNT_NUMBER */
CURSOR   EPAY_CUR (P_DATE IN varchar2)IS
SELECT   V.PARTY_NAME HEADER_PAYEE_NAME,
         TRUNC (V.REMITTANCE_DATE),
         V.PARTY_NUMBER ,
         V.PARTY_NAME PAYEE_NAME,
        -- p.PARTY_NAME,
         V.CURRENCY_ID,
         V.CURRENCY CUR_CODE,
         SUM (V.REMITTANCE_AMOUNT) AMOUNT,
         V.PARTY_ID,
         s.vendor_id,
         s.attribute11 gl_code,
         cmg_get_ccid_fnc (s.attribute11) gl_code_id,
         ps.vendor_site_id,
         ps.invoice_currency_code,
         ps.PAYMENT_METHOD_LOOKUP_CODE
    FROM CMGIN068_CHECK_REGISTERS_VW V, CMGIN.CMG_PAYEE_DETAILS_TBL P ,PO_VENDORS S ,PO_VENDOR_SITES PS
   WHERE TRUNC (REMITTANCE_DATE) =P_DATE
     AND P.PARTY_ID = V.PARTY_ID
      AND s.vendor_id = ps.vendor_id
      AND ps.pay_site_flag = 'Y'
      AND ps.primary_pay_site_flag = 'Y'
      AND v.to_ap_flag in ('N','E')
  --    AND S.VENDOR_NAME=V.PARTY_NAME
      AND s.VENDOR_TYPE_LOOKUP_CODE='INCENTIVE'
      AND P.PARTY_ID=S.ATTRIBUTE4
     -- AND V.PARTY_NUMBER='E'
GROUP BY V.PARTY_NAME,
         TRUNC (V.REMITTANCE_DATE),
         V.PARTY_NUMBER,
         V.PARTY_NAME,
         V.CURRENCY_ID,
         V.CURRENCY,
         NVL (V.TAX_AMOUNT, 0),
         V.PARTY_ID,s.vendor_id,
         s.attribute11 ,
         cmg_get_ccid_fnc (s.attribute11),
         ps.vendor_site_id,
         ps.invoice_currency_code,
         ps.PAYMENT_METHOD_LOOKUP_CODE
ORDER BY V.PARTY_NAME;



      v_user_id          NUMBER             DEFAULT (fnd_global.user_id);
      v_username         VARCHAR2 (20);
      v_email            VARCHAR2 (30);
      v_req_id           NUMBER           DEFAULT (fnd_global.conc_request_id
                                                  );
--v_dirpath       VARCHAR(80);
      v_outpath          VARCHAR2 (80);
      v_outfilename      VARCHAR2 (35);
      v_outfile          UTL_FILE.file_type;
      v_header           VARCHAR2 (2500);
      v_line             VARCHAR2 (2500);
      v_subject          VARCHAR2 (100)     := 'Incentive to AP E-Payment detail file';
      v_record_errored   NUMBER             := 0;
      v_record_count     NUMBER             := 0;
      v_record_amount    NUMBER             := 0;
      v_total_amount     NUMBER             := 0;
   BEGIN
      SELECT user_name, email_address
        INTO v_username, v_email
        FROM fnd_user
       WHERE user_id = v_user_id;

      cmg_utility_pck.write_output
                                 (   '+--------------------------------------'
                                  || '------------+'
                                 );
      cmg_utility_pck.write_output ('CMG Applications');
      cmg_utility_pck.write_output ('Copyright (c) Comag Marketing Group');
      cmg_utility_pck.write_output
                                  (   '+-------------------------------------'
                                   || '--------------+'
                                  );
      cmg_utility_pck.write_output (   'Current System Time is : '
                                    || TO_CHAR (SYSDATE,
                                                'DD-MON-YYYY HH24:MM:SS'
                                               )
                                   );
      cmg_utility_pck.write_output (' ');
      cmg_utility_pck.write_output
                                  (   '+-------------------------------------'
                                   || '--------------+'
                                  );


      cmg_utility_pck.write_output ('test1'|| P_DATE);


      fnd_profile.get ('CMG_OUTGOING_PATH', v_outpath);
      v_outfilename :=
             'RIN_Paydetails_' || TO_CHAR (SYSDATE, 'YYMMDDHHMISS')
             || '.csv';
      v_outfile := UTL_FILE.fopen (v_outpath, v_outfilename, 'w');
      UTL_FILE.put_line (v_outfile, ',Incentive TO AP E-Payment Details');
      v_header := 'Party Number,Party Name,Error Message';
      UTL_FILE.put_line (v_outfile, v_header);


      FOR remit_rec IN epay_cur(P_DATE)
      LOOP
         IF remit_rec.amount > 0
         THEN
            IF remit_rec.gl_code_id IS NULL
            THEN
               UPDATE cmgin .cmg_remittances_tbl
                  SET to_ap_flag = 'E'
                WHERE party_id=remit_rec.party_id
                and trunc(remittance_date)=p_date;

               --  cmg_utility_pck.write_output ('Remittance Id:        Publisher Name:                 Error Message');
               --  cmg_utility_pck.write_output ( remit_rec.remittance_number||'                  '||remit_rec.publisher_name||'              '||
                 --                                          'GL Code/Vendor Pay site id is missing please verify the Supplier Setup');
               v_line :=
                     remit_rec.PARTY_number
                  || ','
                  || remit_rec.PAYEE_NAME
                  || ','
                  || 'GL Code is missing please verify the Supplier Setup/GL Code Combinations';
               UTL_FILE.put_line (v_outfile, v_line);
               v_record_errored := v_record_errored + 1;
            ELSE                                  --IF REMIT_REC.AMOUNT>0 THEN
               -- INSERT AN INVOICE HEADER --
               INSERT INTO ap_invoices_interface
                           (invoice_id,
                            invoice_num,
                            invoice_type_lookup_code, vendor_id,
                            vendor_site_id, invoice_amount,
                            invoice_currency_code, SOURCE,
                            GROUP_ID,
                            payment_method_lookup_code
                           )
                    VALUES (ap_invoices_interface_s.NEXTVAL,
                            remit_rec.party_number||'_'||p_date|| '_RINPAY',
                                                               --REMITTANCE_ID
                            'STANDARD',                           -- DEFAULTED
                                       remit_rec.vendor_id,
                                                       -- PO_VENDORS.VENDOR_ID
                            remit_rec.vendor_site_id,
                                                  -- PO_VENDORS.VENDOR_SITE_ID
                                                     remit_rec.amount,
                            remit_rec.invoice_currency_code,
                                            --PO_VENDORS.INVOICE_CURRENCY_CODE
                                                            'RIN',
                               -- AP_LOOKUP_CODES WHERE LOOKUP_TYPE = 'SOURCE'
                            'RIN_GROUP',
                            remit_rec.PAYMENT_METHOD_LOOKUP_CODE
                           );

               -- INSERT INVOICE LINE --
               INSERT INTO ap_invoice_lines_interface
                           (invoice_id,
                            invoice_line_id, line_number,
                            line_type_lookup_code, amount,
                            dist_code_combination_id
                           )
                    VALUES (ap_invoices_interface_s.CURRVAL,
                            ap_invoice_lines_interface_s.NEXTVAL, 1,
                            'ITEM', remit_rec.amount,
                            remit_rec.gl_code_id
                           );

                UPDATE cmgin .cmg_remittances_tbl
                  SET to_ap_flag = 'P'
                WHERE party_id=remit_rec.party_id
                and trunc(remittance_date)=p_date;


               v_record_count := v_record_count + 1;
               v_total_amount := remit_rec.amount + v_total_amount;




            END IF;
         END IF;

         COMMIT;
      END LOOP;

      --  cmg_utility_pck.write_output ('Total Remittances Inserted:'||v_record_count);
      --  cmg_utility_pck.write_output ('Total Amount:'||v_record_amount);

      UTL_FILE.put_line(v_outfile,'                                     ');
      UTL_FILE.put_line(v_outfile,'                                     ');
      UTL_FILE.put_line(v_outfile,'                                     ');
      UTL_FILE.put_line(v_outfile,'                                     ');
      UTL_FILE.put_line (v_outfile,
                         'Total Remittances Inserted:   ' || v_record_count
                        );
      UTL_FILE.put_line (v_outfile, 'Total Amount:     ' || v_total_amount);
      UTL_FILE.put_line (v_outfile,
                         'Total Remittances Errored:   ' || v_record_errored
                        );

      COMMIT;

     UTL_FILE.fclose (v_outfile);

      IF v_email IS NULL
      THEN
         cmg_utility_pck.write_output('Email address is not setup for this user. '
                            || 'Please contact the setup administrator'
                           );


      ELSE
         cmg_utility_pck.write_output('File created successfully');
         -- email the detail file to email address for this user.
         cmg_abc_submit_pck.main (v_req_id,
                                  v_outpath || '/' || v_outfilename,
                                  v_outfilename,
                                  v_subject,
                                  v_email
                                 );

         cmg_utility_pck.write_output('File emailed successfully');
      END IF;


      cmg_utility_pck.write_output
                                 (   '+--------------------------------------'
                                  || '------------+'
                                 );
      cmg_utility_pck.write_output ('Concurrent Request Completed');
      cmg_utility_pck.write_output
                                  (   '+-------------------------------------'
                                   || '--------------+'
                                  );
      cmg_utility_pck.write_output (   'Current System Time is : '
                                    || TO_CHAR (SYSDATE,
                                                'DD-MON-YYYY HH24:MM:SS'
                                               )
                                   );
      cmg_utility_pck.write_output (' ');
      cmg_utility_pck.write_output
                                  (   '+-------------------------------------'
                                   || '--------------+'
                                  );
   EXCEPTION
      WHEN OTHERS
      THEN
         error_msg := SQLCODE || '            ' || SQLERRM;
         cmg_utility_pck.write_output (error_msg);
   END cmg_incen_epay_to_ap_main_prc;                      --  end of procedure
END cmg_incen_epay_to_ap_pck;                                 -- end of package
/

Submit Concurrent Program from Back End

DECLARE
l_request_id NUMBER;
BEGIN
fnd_global.apps_initialize (user_id=>3049
,resp_id=>21623
,resp_appl_id=>660);
l_request_id := FND_REQUEST.SUBMIT_REQUEST
                ('ONT'         ,
                 'OEOIMP'     ,
                 'Order Import',
                  sysdate - 1,
                 FALSE        ,
                 1026     ,  -- order source
                 null,           -- orig_sys_document_ref
                 null        ,   -- ???
                 'N',            -- validate only flag
                 null,
                 3     -- number of instances
                 );
COMMIT;
IF l_request_id = 0 THEN
dbms_output.put_line('Request not submitted error '|| fnd_message.get);
ELSE
dbms_output.put_line('Request submitted successfully request id ' || l_request_id);
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unexpected errro ' || SQLERRM);
END;

SELECT DISTINCT fr.responsibility_id,responsibility_name,

    frx.application_id

     FROM apps.fnd_responsibility frx,

    apps.fnd_responsibility_tl fr

    WHERE fr.responsibility_id = frx.responsibility_id

  AND LOWER (fr.responsibility_name) LIKE LOWER('Order%');

DECLARE
L_REQUEST_ID NUMBER;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (USER_ID=>3049
,RESP_ID=>50471
,RESP_APPL_ID=>20044);
L_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST
                ('FND'         ,-- application Short name
                 'APPS_TEST1'     ,--Concurrent program Short name
                 'APPS_TEST',-- description
                  SYSDATE - 1,
                 FALSE      
                 );
COMMIT;
IF L_REQUEST_ID = 0 THEN
DBMS_OUTPUT.PUT_LINE('Request not submitted error '|| FND_MESSAGE.GET);
ELSE
DBMS_OUTPUT.PUT_LINE('Request submitted successfully request id ' || L_REQUEST_ID);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected errro ' || SQLERRM);
END;

Interface

CREATE OR REPLACE PACKAGE APPS.CMG_MAG_PUB_PAY AS
/*
   NAME:       CMG_MAG_PUB_PAY
   PURPOSE:    THIS PACKAGE GENERATES A CSV REPORT FOR MAGCORE PUBLISHER PAYABLES.

   REVISIONS:
   VER        DATE        AUTHOR           DESCRIPTION
   ---------  ----------  ---------------  ------------------------------------
   1.0        05/31/2017  SIDDHARTH          MAGCORE PUBLLISHER PAYABLES
*/
PROCEDURE CMG_MAGCORE_PUB_PAY( P_ERRBUF OUT VARCHAR2,
                            P_RETCODE OUT VARCHAR2);
                         
PROCEDURE CMG_MAGG_PUB_PAY( P_ERRBUF OUT VARCHAR2,
                            P_RETCODE OUT VARCHAR2);

PROCEDURE CMG_CORE_PUB_PAY( P_ERRBUF OUT VARCHAR2,
                            P_RETCODE OUT VARCHAR2);
END CMG_MAG_PUB_PAY;
/


CREATE OR REPLACE PACKAGE BODY APPS.CMG_MAG_PUB_PAY AS
/*
   NAME:       CMG_MAG_PUB_PAY
   PURPOSE:  

   REVISIONS:
   VER        DATE        AUTHOR           DESCRIPTION
   ---------  ----------  ---------------  ------------------------------------
   1.0        05/31/2017  SIDDHARTH        MAGCORE PUBLISHER PAYABLES.
*/

PROCEDURE CMG_MAGG_PUB_PAY( P_ERRBUF OUT VARCHAR2,
                             P_RETCODE OUT VARCHAR2) AS
--************ HEADER ONLY QUERY ********************
 CURSOR ISS_CUR  IS
 SELECT
  CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_ID CTR_ID,
  RTRIM(TO_CHAR(CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_START_DATE,'DD-MON-YY') || ' thru ' || TO_CHAR(CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_END_DATE,'DD-MON-YY')) START_TO_END,
  CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.PUBLISHER_NAME PUBLISHER_NAME,
  CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_AGREEMENT_DATE CTR_AGREEMENT_DATE,
  CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_START_DATE     CTR_START_DATE,
  CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_END_DATE       CTR_END_DATE,
  CMGPPA.CMG_PPA_CONTRACT_TBL.TERMINATION_STATUS TERMINATION_STATUS,
  REPLACE(REPLACE(CMGPPA.CMG_PPA_CONTRACT_TBL.TERMINATION_DETAILS,CHR(13),''),CHR(10),' ') TERMINATION_DETAILS,
  CMGPPA.CMG_PPA_CONTRACT_TBL.DIST_TERRITORY DIST_TERRITORY,
  CMGPPA.CMG_PPA_CONTRACT_TBL.SPL_BROKERAGE_FLAG SPL_BROKERAGE_FLAG
FROM
  CMGPPA.CMG_PPA_CONTRACT_TBL,
  CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL
WHERE
  ( CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.HEADER_CTR_ID=CMGPPA.CMG_PPA_CONTRACT_TBL.HEADER_CTR_ID  )
  AND
  (
   CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_END_DATE  >  TO_DATE('01-01-2016','MM/DD/YYYY')
   AND
   CMGPPA.CMG_PPA_CONTRACT_TBL.TERMINATION_STATUS  NOT IN  ('T','V')
  );

 V_REQ_ID                 NUMBER DEFAULT (FND_GLOBAL.CONC_REQUEST_ID);
 V_USER_NAME              VARCHAR2 (80);
 V_USER_ID                NUMBER DEFAULT (FND_GLOBAL.USER_ID);
 V_OUTFILENAME            VARCHAR2 (60);
 V_EMAIL                  VARCHAR2 (40);
 V_SUBJECT                VARCHAR2 (100) := 'CMG_MAG_PUB_PAYABLES-2 -->'|| TO_CHAR (SYSDATE,  'YYMMDDHHMISS');
 V_OUTPATH                VARCHAR2 (100);
 V_OUTFILE                UTL_FILE.FILE_TYPE;
 V_HEADER                 VARCHAR2(25000);
 V_LINE                   VARCHAR2 (25000);

 BEGIN

      P_ERRBUF := NULL;
      P_RETCODE := 0;


     SELECT USER_NAME,EMAIL_ADDRESS
        INTO V_USER_NAME,V_EMAIL
        FROM FND_USER
       WHERE USER_ID = V_USER_ID;

     FND_PROFILE.GET ('CMG_OUTGOING_PATH', V_OUTPATH);
     V_OUTFILENAME :='CMG_Mag_Pub_Pay'||TO_CHAR (SYSDATE, 'MMYYYY')|| '.csv';
     V_OUTFILE := UTL_FILE.FOPEN (V_OUTPATH, V_OUTFILENAME, 'w',max_linesize => 32767);


      V_HEADER := 'CTR_ID,START_TO_END,PUBLISHER_NAME,CTR_AGREEMENT_DATE,CTR_START_DATE,CTR_END_DATE,TERMINATION_STATUS,TERMINATION_DETAILS,DIST_TERRITORY,SPL_BROKERAGE_FLAG';

      UTL_FILE.PUT_LINE (V_OUTFILE, V_HEADER);

      CMG_UTILITY_PCK.WRITE_OUTPUT (
         '+--------------------------------------' || '-------------+');

      CMG_UTILITY_PCK.WRITE_OUTPUT ('Oracle Applications');
      CMG_UTILITY_PCK.WRITE_OUTPUT ('Copyright (c) Comag Marketing Group');
      CMG_UTILITY_PCK.WRITE_OUTPUT (
         '+-------------------------------------' || '--------------+');
      CMG_UTILITY_PCK.WRITE_OUTPUT (
            'Current System Time is : '
         || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
      CMG_UTILITY_PCK.WRITE_OUTPUT (' ');
      CMG_UTILITY_PCK.WRITE_OUTPUT (
         '+-------------------------------------' || '--------------+');

  FOR ISS_REC IN ISS_CUR LOOP

     V_LINE :=  ISS_REC.CTR_ID
                ||','
                ||ISS_REC.START_TO_END
                ||','              
                ||'"'
                ||ISS_REC.PUBLISHER_NAME
                ||'"'
                ||','
                ||ISS_REC.CTR_AGREEMENT_DATE
                ||','
                ||ISS_REC.CTR_START_DATE
                ||','
                ||ISS_REC.CTR_END_DATE
                ||','
                ||ISS_REC.TERMINATION_STATUS
                ||','
                ||'"'
                ||ISS_REC.TERMINATION_DETAILS
                ||'"'
                ||','
                ||'"'
                ||ISS_REC.DIST_TERRITORY
                ||'"'
                ||','
                ||ISS_REC.SPL_BROKERAGE_FLAG;
             
   UTL_FILE.PUT_LINE (V_OUTFILE, V_LINE);

END LOOP;

  UTL_FILE.FCLOSE (V_OUTFILE);

   IF V_EMAIL IS NULL
      THEN
         CMG_UTILITY_PCK.WRITE_OUTPUT (
               'EMAIL ADDRESS IS NOT SETUP FOR THIS USER'
            || 'PLEASE CONTACT THE SETUP ADMINISTRATOR');
      ELSE
         CMG_UTILITY_PCK.WRITE_OUTPUT ('FILE CREATED SUCCESSFULLY');
         -- EMAIL THE DETAIL FILE TO EMAIL ADDRESS FOR THIS USER.
         CMG_ABC_SUBMIT_PCK.MAIN (V_REQ_ID,
                                  V_OUTPATH || '/' || V_OUTFILENAME,
                                  V_OUTFILENAME,
                                  V_SUBJECT,
                                  V_EMAIL);


         CMG_UTILITY_PCK.WRITE_OUTPUT ('FILE EMAILED SUCCESSFULLY');
      END IF;

      CMG_UTILITY_PCK.WRITE_OUTPUT ('');
      CMG_UTILITY_PCK.WRITE_OUTPUT ('');
      CMG_UTILITY_PCK.WRITE_OUTPUT ('+-------------------------------------' || '--------------+');
   
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID OPERATION');
WHEN UTL_FILE.INVALID_PATH THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID PATH');
WHEN UTL_FILE.INVALID_MODE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID MODE');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID FILE');
WHEN UTL_FILE.READ_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'READ ERROR');
WHEN UTL_FILE.INTERNAL_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INTERNAL ERROR');
WHEN OTHERS THEN
P_RETCODE:=2;
P_ERRBUF := SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'OTHER ERROR');


END CMG_MAGG_PUB_PAY;

PROCEDURE CMG_MAGCORE_PUB_PAY ( P_ERRBUF OUT VARCHAR2,
                            P_RETCODE OUT VARCHAR2) AS
--******************************QUERY FOR SENDING TO KABLE FOR MAGCORE PUBLISHER PAYABLES*************************************
 CURSOR ISS_CUR  IS
 SELECT
  CMGPPA.CMG_PPA_CONTRACT_TBL.ORG_ID ORG_ID,
  CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.VENDOR_SITE_ID VENDOR_SITE_ID,
  TRIM(CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.PUBLISHER_NAME) PUBLISHER_NAME,
  CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.HEADER_CTR_ID HEADER_CTR_ID,
  CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_ID CTR_ID,
  CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SUBCTR_ID SUBCTR_ID,
  CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_START_DATE CTR_START_DATE,
  CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_END_DATE CTR_END_DATE,
  CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_AGREEMENT_DATE CTR_AGREEMENT_DATE,
  CMGPPA.CMG_PPA_CONTRACT_TBL.TERMINATION_STATUS TERMINATION_STATUS,
  CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.START_DATE START_DATE,
  REPLACE(REPLACE(CMGPPA.CMG_PPA_CONTRACT_TBL.RENEWAL_INFORMATION,CHR(13),''),CHR(10),' ') RENEWAL_INFORMATION,
  CMGPPA.CMG_PPA_SUBCONTRACT_TBL.BROK_BASED_ON BROK_BASED_ON,
  CMGPPA.CMG_PPA_SUBCONTRACT_TBL.BROK_BY_SET_FEE BROK_BY_SET_FEE,
  CMGPPA.CMG_PPA_SUBCONTRACT_TBL.STD_BROK_PERCENT STD_BROK_PERCENT,
  CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SET_FEE_DESCRIPTION SET_FEE_DESCRIPTION,
  CMGPPA.CMG_PPA_BROK_DATE_ISS_TBL.BROK_START_DATE BROK_START_DATE,
  CMGPPA.CMG_PPA_BROK_DATE_ISS_TBL.BROK_END_DATE BROK_END_DATE,
  CMGPPA.CMG_PPA_BROK_DATE_ISS_TBL.BROK_PERCENT BROK_PERCENT,
  CMGPPA.CMG_PPA_SUBCONTRACT_TBL.DISCOUNT_PERCENT DISCOUNT_PERCENT,
  CMGPPA.CMG_PPA_SUBCONTRACT_TBL.STD_COMM_TYPE STD_COMM_TYPE,
  CMGPPA.CMG_PPA_EXCEPTIONS_TBL.WHOLESALER_NAME WHOLESALER_NAME,
  CMGPPA.CMG_PPA_EXCEPTIONS_TBL.EXCP_BROK_PERCENT EXCP_BROK_PERCENT,
  CMGPPA.CMG_PPA_EXCEPTIONS_TBL.EXCP_BASED_ON EXCP_BASED_ON,
  CMGPPA.CMG_PPA_EXCEPTIONS_TBL.EXCP_DISCOUNT_PERCENT EXCP_DISCOUNT_PERCENT
FROM
  CMGPPA.CMG_PPA_CONTRACT_TBL,
  CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL,
  CMGPPA.CMG_PPA_SUBCONTRACT_TBL,
  CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL,
  CMGPPA.CMG_PPA_BROK_DATE_ISS_TBL,
  CMGPPA.CMG_PPA_EXCEPTIONS_TBL
WHERE
  ( CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.HEADER_CTR_ID=CMGPPA.CMG_PPA_CONTRACT_TBL.HEADER_CTR_ID  )
  AND  ( CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_ID=CMGPPA.CMG_PPA_SUBCONTRACT_TBL.CTR_ID  )
  AND  ( CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SUBCTR_ID=CMGPPA.CMG_PPA_EXCEPTIONS_TBL.SUBCTR_ID(+)  )
  AND  ( CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SUBCTR_ID=CMGPPA.CMG_PPA_BROK_DATE_ISS_TBL.SUBCTR_ID(+)  )
  AND  ( CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SUBCTR_ID=CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.SUBCTR_ID  )
  AND
  (
   CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_END_DATE  >=  TO_DATE('01-01-2016','MM/DD/YYYY')
   AND
   CMGPPA.CMG_PPA_CONTRACT_TBL.TERMINATION_STATUS  IN  ( 'A','P'  )
  );

 V_REQ_ID                 NUMBER DEFAULT (FND_GLOBAL.CONC_REQUEST_ID);
 V_USER_NAME              VARCHAR2 (80);
 V_USER_ID                NUMBER DEFAULT (FND_GLOBAL.USER_ID);
 V_OUTFILENAME            VARCHAR2 (60);
 V_EMAIL                  VARCHAR2 (40);
 V_SUBJECT                VARCHAR2 (100) := 'CMG_MAG_PUB_PAYABLES-1 -->'|| TO_CHAR (SYSDATE,  'YYMMDDHHMISS');
 V_OUTPATH                VARCHAR2 (100);
 V_OUTFILE                UTL_FILE.FILE_TYPE;
 V_HEADER                 VARCHAR2(25000);
 V_LINE                   VARCHAR2 (25000);

 BEGIN

      P_ERRBUF := NULL;
      P_RETCODE := 0;


     SELECT USER_NAME,EMAIL_ADDRESS
        INTO V_USER_NAME,V_EMAIL
        FROM FND_USER
       WHERE USER_ID = V_USER_ID;

     FND_PROFILE.GET ('CMG_OUTGOING_PATH', V_OUTPATH);
     V_OUTFILENAME :='CMG_Mag_Pub_Pay'||TO_CHAR (SYSDATE, 'MMYYYY')|| '.csv';
     V_OUTFILE := UTL_FILE.FOPEN (V_OUTPATH, V_OUTFILENAME, 'w');


      V_HEADER := 'ORG_ID,VENDOR_SITE_ID,HEADER_CTR_ID,CTR_ID,SUBCTR_ID,CTR_START_DATE,CTR_END_DATE,CTR_AGREEMENT_DATE,TERMINATION_STATUS,START_DATE,RENEWAL_INFORMATION,BROK_BASED_ON,BROK_BY_SET_FEE,STD_BROK_PERCENT,SET_FEE_DESCRIPTION,BROK_START_DATE,BROK_END_DATE,BROK_PERCENT,DISCOUNT_PERCENT,STD_COMM_TYPE,WHOLESALER_NAME,EXCP_BROK_PERCENT,EXCP_BASED_ON,EXCP_DISCOUNT_PERCENT';


      UTL_FILE.PUT_LINE (V_OUTFILE, V_HEADER);

      CMG_UTILITY_PCK.WRITE_OUTPUT (
         '+--------------------------------------' || '-------------+');

      CMG_UTILITY_PCK.WRITE_OUTPUT ('Oracle Applications');
      CMG_UTILITY_PCK.WRITE_OUTPUT ('Copyright (c) Comag Marketing Group');
      CMG_UTILITY_PCK.WRITE_OUTPUT (
         '+-------------------------------------' || '--------------+');
      CMG_UTILITY_PCK.WRITE_OUTPUT (
            'Current System Time is : '
         || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
      CMG_UTILITY_PCK.WRITE_OUTPUT (' ');
      CMG_UTILITY_PCK.WRITE_OUTPUT (
         '+-------------------------------------' || '--------------+');

  FOR ISS_REC IN ISS_CUR LOOP

     V_LINE :=  ISS_REC.ORG_ID
                ||','
                ||ISS_REC.VENDOR_SITE_ID
                ||','
                ||'"'
                ||ISS_REC.PUBLISHER_NAME
                ||'"'
                ||','
                ||ISS_REC.HEADER_CTR_ID
                ||','
                ||ISS_REC.CTR_ID
                ||','
                ||ISS_REC.SUBCTR_ID
                ||','
                ||ISS_REC.CTR_START_DATE
                ||','
                ||ISS_REC.CTR_END_DATE
                ||','
                ||ISS_REC.CTR_AGREEMENT_DATE
                ||','
                ||'"'
                ||ISS_REC.TERMINATION_STATUS
                ||'"'
                ||','
                ||ISS_REC.START_DATE
                ||','
                ||'"'
                ||ISS_REC.RENEWAL_INFORMATION
                ||'"'
                ||','
                ||'"'
                ||ISS_REC.BROK_BASED_ON
                ||'"'
                ||','
                ||'"'
                ||ISS_REC.BROK_BY_SET_FEE
                ||'"'
                ||','
                ||'"'
                ||ISS_REC.STD_BROK_PERCENT
                ||'"'
                ||','
                ||'"'
                ||ISS_REC.SET_FEE_DESCRIPTION
                ||'"'
                ||','
                ||ISS_REC.BROK_START_DATE
                ||','
                ||ISS_REC.BROK_END_DATE
                ||','
                ||ISS_REC.BROK_PERCENT
                ||','
                ||'"'
                ||ISS_REC.DISCOUNT_PERCENT
                ||'"'
                ||','
                ||'"'
                ||ISS_REC.STD_COMM_TYPE
                ||'"'
                ||','
                ||'"'
                ||ISS_REC.WHOLESALER_NAME
                ||'"'
                ||','
                ||'"'
                ||ISS_REC.EXCP_BROK_PERCENT
                ||'"'
                ||','
                ||'"'
                ||ISS_REC.EXCP_BASED_ON
                ||'"'
                ||','
                ||ISS_REC.EXCP_DISCOUNT_PERCENT;

   UTL_FILE.PUT_LINE (V_OUTFILE, V_LINE);

END LOOP;

  UTL_FILE.FCLOSE (V_OUTFILE);

   IF V_EMAIL IS NULL
      THEN
         CMG_UTILITY_PCK.WRITE_OUTPUT (
               'EMAIL ADDRESS IS NOT SETUP FOR THIS USER'
            || 'PLEASE CONTACT THE SETUP ADMINISTRATOR');
      ELSE
         CMG_UTILITY_PCK.WRITE_OUTPUT ('FILE CREATED SUCCESSFULLY');
         -- EMAIL THE DETAIL FILE TO EMAIL ADDRESS FOR THIS USER.
         CMG_ABC_SUBMIT_PCK.MAIN (V_REQ_ID,
                                  V_OUTPATH || '/' || V_OUTFILENAME,
                                  V_OUTFILENAME,
                                  V_SUBJECT,
                                  V_EMAIL);


         CMG_UTILITY_PCK.WRITE_OUTPUT ('FILE EMAILED SUCCESSFULLY');
      END IF;

      CMG_UTILITY_PCK.WRITE_OUTPUT ('');
      CMG_UTILITY_PCK.WRITE_OUTPUT ('');
      CMG_UTILITY_PCK.WRITE_OUTPUT ('+-------------------------------------' || '--------------+');
   
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID OPERATION');
WHEN UTL_FILE.INVALID_PATH THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID PATH');
WHEN UTL_FILE.INVALID_MODE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID MODE');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID FILE');
WHEN UTL_FILE.READ_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'READ ERROR');
WHEN UTL_FILE.INTERNAL_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INTERNAL ERROR');
WHEN OTHERS THEN
P_RETCODE:=2;
P_ERRBUF := SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'OTHER ERROR');


END CMG_MAGCORE_PUB_PAY;

PROCEDURE CMG_CORE_PUB_PAY( P_ERRBUF OUT VARCHAR2,
                            P_RETCODE OUT VARCHAR2) AS
--*****************TITLES ATTACHED TO CONTRACT QUERY*******************************
 CURSOR ISS_CUR  IS
 SELECT
  CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SUBCTR_ID SUBCTR_ID,
  CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.PUBLISHER_NAME PUBLISHER_NAME,
  CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.VENDOR_SITE_ID VENDOR_SITE_ID,
  CMGPPA.CMG_PPA_TITLE_DIM_TBL.TITLE TITLE,
  CMGPPA.CMG_PPA_TITLE_DIM_TBL.BIPAD BIPAD,
  CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.ASSIGNED ASSIGNED,
  CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.START_DATE START_DATE,
  CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.END_DATE END_DATE,
  CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.LAST_ISSUE LAST_ISSUE,
  CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SUBCTR_STATUS SUBCTR_STATUS,
  CMGPPA.CMG_PPA_SUBCONTRACT_TBL.VALID_FLAG VALID_FLAG
FROM
  CMGPPA.CMG_PPA_SUBCONTRACT_TBL,
  CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL,
  CMGPPA.CMG_PPA_TITLE_DIM_TBL,
  CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL,
  CMGPPA.CMG_PPA_CONTRACT_TBL
WHERE
  ( CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.HEADER_CTR_ID=CMGPPA.CMG_PPA_CONTRACT_TBL.HEADER_CTR_ID  )
  AND  ( CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_ID=CMGPPA.CMG_PPA_SUBCONTRACT_TBL.CTR_ID  )
  AND  ( CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SUBCTR_ID=CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.SUBCTR_ID  )
  AND  ( CMGPPA.CMG_PPA_TITLE_DIM_TBL.TITLE_DIM_ID=CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.TITLE_DIM_ID  )
  AND
  (
   CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_END_DATE  >=  TO_DATE('01-01-2016','MM/DD/YYYY')
   AND
   CMGPPA.CMG_PPA_CONTRACT_TBL.TERMINATION_STATUS  IN  ( 'A','P'  )
   AND
   CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.ASSIGNED  IN  ( 'Y'  )
  );

 V_REQ_ID                 NUMBER DEFAULT (FND_GLOBAL.CONC_REQUEST_ID);
 V_USER_NAME              VARCHAR2 (80);
 V_USER_ID                NUMBER DEFAULT (FND_GLOBAL.USER_ID);
 V_OUTFILENAME            VARCHAR2 (60);
 V_EMAIL                  VARCHAR2 (40);
 V_SUBJECT                VARCHAR2 (100) := 'CMG_MAG_PUB_PAYABLES-3'|| TO_CHAR (SYSDATE,  'YYMMDDHHMISS');
 V_OUTPATH                VARCHAR2 (100);
 V_OUTFILE                UTL_FILE.FILE_TYPE;
 V_HEADER                 VARCHAR2(25000);
 V_LINE                   VARCHAR2 (25000);

 BEGIN

      P_ERRBUF := NULL;
      P_RETCODE := 0;


     SELECT USER_NAME,EMAIL_ADDRESS
        INTO V_USER_NAME,V_EMAIL
        FROM FND_USER
       WHERE USER_ID = V_USER_ID;

     FND_PROFILE.GET ('CMG_OUTGOING_PATH', V_OUTPATH);
     V_OUTFILENAME :='CMG_Core_Pub_Pay'||TO_CHAR (SYSDATE, 'MMYYYY')|| '.csv';
     V_OUTFILE := UTL_FILE.FOPEN (V_OUTPATH, V_OUTFILENAME, 'w');


      V_HEADER := 'SUBCTR_ID,PUBLISHER_NAME,VENDOR_SITE_ID,TITLE,BIPAD,ASSIGNED,START_DATE,END_DATE,LAST_ISSUE,SUBCTR_STATUS,VALID_FLAG';

      UTL_FILE.PUT_LINE (V_OUTFILE, V_HEADER);

      CMG_UTILITY_PCK.WRITE_OUTPUT (
         '+--------------------------------------' || '-------------+');

      CMG_UTILITY_PCK.WRITE_OUTPUT ('Oracle Applications');
      CMG_UTILITY_PCK.WRITE_OUTPUT ('Copyright (c) Comag Marketing Group');
      CMG_UTILITY_PCK.WRITE_OUTPUT (
         '+-------------------------------------' || '--------------+');
      CMG_UTILITY_PCK.WRITE_OUTPUT (
            'Current System Time is : '
         || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
      CMG_UTILITY_PCK.WRITE_OUTPUT (' ');
      CMG_UTILITY_PCK.WRITE_OUTPUT (
         '+-------------------------------------' || '--------------+');

  FOR ISS_REC IN ISS_CUR LOOP

     V_LINE :=  ISS_REC.SUBCTR_ID
                ||','
                ||'"'
                ||ISS_REC.PUBLISHER_NAME
                ||'"'
                ||','
                ||ISS_REC.VENDOR_SITE_ID
                ||','
                ||'"'
                ||ISS_REC.TITLE
                ||'"'
                ||','
                ||ISS_REC.BIPAD
                ||','
                ||ISS_REC.ASSIGNED
                ||','
                ||ISS_REC.START_DATE
                ||','
                ||ISS_REC.END_DATE
                ||','
                ||ISS_REC.LAST_ISSUE
                ||','
                ||ISS_REC.SUBCTR_STATUS
                ||','
                ||ISS_REC.VALID_FLAG;
             
   UTL_FILE.PUT_LINE (V_OUTFILE, V_LINE);

END LOOP;

  UTL_FILE.FCLOSE (V_OUTFILE);

   IF V_EMAIL IS NULL
      THEN
         CMG_UTILITY_PCK.WRITE_OUTPUT (
               'EMAIL ADDRESS IS NOT SETUP FOR THIS USER'
            || 'PLEASE CONTACT THE SETUP ADMINISTRATOR');
      ELSE
         CMG_UTILITY_PCK.WRITE_OUTPUT ('FILE CREATED SUCCESSFULLY');
         -- EMAIL THE DETAIL FILE TO EMAIL ADDRESS FOR THIS USER.
         CMG_ABC_SUBMIT_PCK.MAIN (V_REQ_ID,
                                  V_OUTPATH || '/' || V_OUTFILENAME,
                                  V_OUTFILENAME,
                                  V_SUBJECT,
                                  V_EMAIL);


         CMG_UTILITY_PCK.WRITE_OUTPUT ('FILE EMAILED SUCCESSFULLY');
      END IF;

      CMG_UTILITY_PCK.WRITE_OUTPUT ('');
      CMG_UTILITY_PCK.WRITE_OUTPUT ('');
      CMG_UTILITY_PCK.WRITE_OUTPUT ('+-------------------------------------' || '--------------+');
   

EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID OPERATION');
WHEN UTL_FILE.INVALID_PATH THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID PATH');
WHEN UTL_FILE.INVALID_MODE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID MODE');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID FILE');
WHEN UTL_FILE.READ_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'READ ERROR');
WHEN UTL_FILE.INTERNAL_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INTERNAL ERROR');
WHEN OTHERS THEN
P_RETCODE:=2;
P_ERRBUF := SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'OTHER ERROR');

END CMG_CORE_PUB_PAY;

END CMG_MAG_PUB_PAY;
/