FA Mass Additions Create - draft

SELECT
        to_char(SC.LAST_MASS_ADDITIONS) REQUEST,
        &ACCT_FLEX_BAL_SEG                        COMPANY,
        &ACCT_FLEX_ACCT_SEG                         ACCOUNT,
        &ACCT_FLEX_COST_SEG                         COST_CTR,
        FAMA.VENDOR_NUMBER||' - '||
          PV.VENDOR_NAME                    VENDOR,
        FAMA.INVOICE_NUMBER                 INV,                     FAMA.INVOICE_LINE_NUMBER||' - '||
          FAMA.AP_DISTRIBUTION_LINE_NUMBER  LINE,
        FAMA.INVOICE_DATE                   ID,
        FAMA.PAYABLES_BATCH_NAME            BAT,
        FAMA.DESCRIPTION                    DESCR,
        FAMA.FIXED_ASSETS_COST              AMT,
        API.INVOICE_CURRENCY_CODE            CURR_CODE,
        decode(APID.BASE_AMOUNT,
               Null,to_number(Null),
               APID.AMOUNT)                  BASE_AMT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_company', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMPANY, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_account', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') D_ACCOUNT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR
FROM FA_MASS_ADDITIONS                    FAMA,
        GL_CODE_COMBINATIONS                 DHCC,
        PO_VENDORS                           PV,
        FA_SYSTEM_CONTROLS                   SC,
        AP_INVOICE_DISTRIBUTIONS_ALL         APID,
        AP_INVOICES_ALL                      API 
  WHERE FAMA.POSTING_STATUS              in ('NEW', 'MERGED')
    AND FAMA.BOOK_TYPE_CODE               = :P_BOOK
    AND FAMA.PAYABLES_CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID
    AND PV.VENDOR_ID(+)                   = FAMA.PO_VENDOR_ID
    AND APID.INVOICE_DISTRIBUTION_ID      = FAMA.INVOICE_DISTRIBUTION_ID
    AND API.INVOICE_ID                    = APID.INVOICE_ID
    AND SC.LAST_MASS_ADDITIONS            = FAMA.CREATE_BATCH_ID
    AND FAMA.invoice_payment_id          is null
 union
SELECT
        to_char(SC.LAST_MASS_ADDITIONS) REQUEST,
        &ACCT_FLEX_BAL_SEG                        COMPANY,
        &ACCT_FLEX_ACCT_SEG                         ACCOUNT,
        &ACCT_FLEX_COST_SEG                         COST_CTR,
        FAMA.VENDOR_NUMBER||' - '||
          PV.VENDOR_NAME                     VENDOR,
        FAMA.INVOICE_NUMBER                  INV,
        FAMA.INVOICE_LINE_NUMBER||' - '||
          FAMA.AP_DISTRIBUTION_LINE_NUMBER   LINE,
        FAMA.INVOICE_DATE                    ID,
        FAMA.PAYABLES_BATCH_NAME             BAT,
        FAMA.DESCRIPTION                     DESCR,
        FAMA.FIXED_ASSETS_COST               AMT,
        API.INVOICE_CURRENCY_CODE            CURR_CODE,
        -1 * APHD.AMOUNT                     BASE_AMT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_company', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMPANY, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_account', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') D_ACCOUNT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR
FROM FA_MASS_ADDITIONS                    FAMA,
        GL_CODE_COMBINATIONS                 DHCC,
        PO_VENDORS                           PV,
        FA_SYSTEM_CONTROLS                   SC,
        AP_INVOICES_ALL                      API,
        AP_PAYMENT_HIST_DISTS                APHD
  WHERE FAMA.POSTING_STATUS              in ( 'NEW', 'MERGED')
    AND FAMA.BOOK_TYPE_CODE               = :p_book
    AND FAMA.PAYABLES_CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID
    AND PV.VENDOR_ID(+)                   = FAMA.PO_VENDOR_ID
    AND API.INVOICE_ID                    = FAMA.INVOICE_ID
    AND APHD.INVOICE_PAYMENT_ID           = FAMA.INVOICE_PAYMENT_ID
    AND APHD.PAY_DIST_LOOKUP_CODE         = 'DISCOUNT'
    AND APHD.INVOICE_DISTRIBUTION_ID      = FAMA.INVOICE_DISTRIBUTION_ID
    AND SC.LAST_MASS_ADDITIONS            = FAMA.CREATE_BATCH_ID
    AND FAMA.invoice_payment_id          is not null
ORDER BY
	12,2,3,4,5,6,7